Advertisement
Advertisement


How to check if a column exists in a SQL Server table?


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?

2018/06/03
1
1881
6/3/2018 11:12:42 AM

Accepted Answer

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
2017/04/12
2086
4/12/2017 3:35:20 AM


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?

2014/12/31

Try this...

IF NOT EXISTS(
  SELECT TOP 1 1
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE 
    [TABLE_NAME] = 'Employees'
    AND [COLUMN_NAME] = 'EmployeeID')
BEGIN
  ALTER TABLE [Employees]
    ADD [EmployeeID] INT NULL
END
2011/11/14

For the people who is checking the column existence to drop it.

From SQL Server 2016 you can use new DIE statements instead of big IF wrappers

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name
2019/09/05

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.

2009/06/26

You can use the information schema system views to find out pretty much anything about the tables you're interested in:

SELECT *
  FROM INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = 'yourTableName'
 ORDER BY ORDINAL_POSITION

You can also interrogate views, stored procedures and pretty much anything about the database using the Information_schema views.

2008/09/25

Source: https://stackoverflow.com/questions/133031
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]