How to check if a column exists in a SQL Server table?
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?
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
A more concise version
IF COL_LENGTH('table_name','column_name') IS NULL BEGIN /* Column does not exist or caller does not have permission to view the object */ END
The point about permissions on viewing metadata applies to all answers not just this one.
Note that the first parameter table name to
COL_LENGTH can be in one, two, or three part name format as required.
An example referencing a table in a different database is
One difference with this answer compared to using the metadata views is that metadata functions such as
COL_LENGTH always only return data about committed changes irrespective of the isolation level in effect.
Read more... Read less...
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?
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
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
ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name
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.
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.