How to check if a column exists in a SQL Server ta

Question

I need to add a specific column if it does not exist. I have something like the following, but it always returns false:

IF EXISTS(SELECT *
          FROM   INFORMATION_SCHEMA.COLUMNS
          WHERE  TABLE_NAME = 'myTableName'
                 AND COLUMN_NAME = 'myColumnName') 

How can I check if a column exists in a table of the SQL Server database?

Answer 1

SQL Server 2005 onwards:

IF EXISTS(SELECT 1 FROM sys.columns 
          WHERE Name = N'columnName'
          AND Object_ID = Object_ID(N'schemaName.tableName'))
BEGIN
    -- Column Exists
END

Martin Smith's version is shorter:

IF COL_LENGTH('schemaName.tableName', 'columnName') IS NOT NULL
BEGIN
    -- Column Exists
END

Answer 2

A good friend and colleague of mine showed me how you can also use an IF block with SQL functions OBJECT_ID and COLUMNPROPERTY in SQL SERVER 2005+ to check for a column. You can use something similar to the following:

You can see for yourself here

IF (OBJECT_ID(N'[dbo].[myTable]') IS NOT NULL AND
    COLUMNPROPERTY( OBJECT_ID(N'[dbo].[myTable]'), 'ThisColumnDoesNotExist', 'ColumnId') IS NULL)
BEGIN
    SELECT 'Column does not exist -- You can add TSQL to add the column here'
END

Answer 3

if exists (select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='<table_name>' and COLUMN_NAME='<column_name>')
  begin
    print 'Column you have specified exists'
  end
else
  begin
    print 'Column does not exists'
  end

Answer 4

Tweak the below to suit your specific requirements:

if not exists (select
                     column_name
               from
                     INFORMATION_SCHEMA.columns
               where
                     table_name = 'MyTable'
                     and column_name = 'MyColumn')
    alter table MyTable add MyColumn int

Edit to deal with edit to question: That should work - take a careful look over your code for stupid mistakes; are you querying INFORMATION_SCHEMA on the same database as your insert is being applied to for example? Do you have a typo in your table/column name in either statement?

Answer 5

declare @myColumn   as nvarchar(128)
set @myColumn = 'myColumn'
if not exists (
    select  1
    from    information_schema.columns columns 
    where   columns.table_catalog   = 'myDatabase'
        and columns.table_schema    = 'mySchema' 
        and columns.table_name      = 'myTable' 
        and columns.column_name     = @myColumn
    )
begin
    exec('alter table myDatabase.mySchema.myTable add'
    +'    ['+@myColumn+'] bigint       null')
end

Answer 6

I'd prefer INFORMATION_SCHEMA.COLUMNS over a system table because Microsoft does not guarantee to preserve the system tables between versions. For example, dbo.syscolumns does still work in SQL 2008, but it's deprecated and could be removed at any time in future.