Sometimes, you want to execute certain SQL code only if you know that a specific column exists within a table.
There is an extremely simple way to check if a column exists within a table in SQL Server:
Use the COL_LENGTH system function!
The syntax of the COL_LENGTH system function is as follows:
COL_LENGTH('<table name>', '<column name>')
You basically just pass the name of the table your interested in, and the name of the column within that table you want to check.
This function returns the defined length of the column in bytes, if that column exists. If the column does not exist, the function returns NULL.
We can use the extremely handy IS NOT NULL predicate combined with the COL_LENGTH function to see if our column exists within our table.
Here is an example of the syntax we would use if we want to check if a column called BookID exists within a table called Books, using COL_LENGTH in a very simple IF statement:
IF COL_LENGTH('dbo.Books', 'BookID') IS NOT NULL
BEGIN
/*The 'BookID' column exists within the 'Books' table. Do work accordingly*/
END
ELSE
BEGIN
/*The column DOES NOT EXIST in the table*/
END
If COL_LENGTH returns something, we know the column exists!
Otherwise, the column does not exist in the table!
Notice I outlined the schema name of the table. It’s good to do that to avoid ambiguity.
Here’s the code running in my environment. My Books table does, indeed, have a column called BookID.
The table does not have a column called ‘Spagetti‘:
It’s as simple as that!
Next Steps:
Leave a comment if you found this tutorial helpful!
Read more about the IS NOT NULL predicate and other extremely helpful querying tools you can use with NULL in the following tutorial:
SQL Server NULL: Are you making these 7 mistakes?
An understanding of NULL and how it works in SQL Server is essential for writing accurate queries.
And do you understand why we need to use “IS NOT NULL” and not just “!= NULL”?
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, leave a comment. Or better yet, send me an email!
