When working with SQL Server databases, it’s common to need to check if a specific column exists in a table. For example in scenarios like database migrations, data imports, or when working with dynamic schema designs, where you typically want to check programmatically if the model is complete. Here are three methods to check if a column exists in a SQL Server table, with different T-SQL queries.
Table of Contents
Method 1: Using the Columns System View to check column existence
This first method involves querying the
INFORMATION_SCHEMA.COLUMNS system view, which contains metadata about all columns in the database. To check if a Sales table have the column MonthName, the SQL query would look like this one below.
Note that in this query, we don’t need to specify the schema, but we execute it on the target database.
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'SALES' AND COLUMN_NAME = 'MonthName' ) BEGIN -- The column exists, add some logic here PRINT 'Column exists' END ELSE BEGIN -- The column does not exists, add some logic here PRINT 'Column does not exist' END
Of course, adapt the code by replacing YourTableName and YourColumnName with the appropriate table and column names. The result of the query above gives this in SSMS (in my case the column effectively exists in the Expert-Only database):
Method 2: Use the columns and tables system catalog views
This method utilizes the SQL Server system catalog views to check for the existence of the column. It’s a more system-centric approach.
IF EXISTS ( SELECT 1 FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE t.name = 'SALES' AND c.name = 'MonthName' ) BEGIN -- If the column exists PRINT 'Column exists' END ELSE BEGIN -- If it does not exist PRINT 'Column does not exist' END
Method 3: Use Object_ID and Col_Length functions to check column existence
For a much more straightforward approach, you can use the
COL_LENGTH functions combined. This method is concise and effective, especially for quick checks in scripts.
IF COL_LENGTH('SALES', 'MonthName') IS NOT NULL PRINT 'Column exists' ELSE PRINT 'Column does not exist' ;
Combine these queries with dynamic SQL for larger checks
Each method has its own advantages, depending on the context of use and the specific requirements of your database environment. It’s always a good idea to choose the one that best fits your scenario and conforms to the best practices in your organization.
I would recommend for example to include it in a large JOIN query to log in a table all the schemas, tables and column names that do not exists for better tracking. And also to add a data type check to make sure the structure is also the same. Now to go further and continue on object checking in MS SQL, here is how to list all SQL Server tables with one query.