How to check if a column exists in SQL Server ?

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.

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 OBJECT_ID and 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.

https://expert-only.com/en/t-sql/query-list-all-sql-server-tables/