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
How to check if a column exists in a SQL Server table?
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.
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 are checking the column existence before dropping it.
From SQL Server 2016 you can use new DIE statements instead of big
ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name
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.
Try something like:
CREATE FUNCTION ColumnExists(@TableName varchar(100), @ColumnName varchar(100)) RETURNS varchar(1) AS BEGIN DECLARE @Result varchar(1); IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName) BEGIN SET @Result = 'T' END ELSE BEGIN SET @Result = 'F' END RETURN @Result; END GO GRANT EXECUTE ON [ColumnExists] TO [whoever] GO
Then use it like this:
IF ColumnExists('xxx', 'yyyy') = 'F' BEGIN ALTER TABLE xxx ADD yyyyy varChar(10) NOT NULL END GO
It should work on both SQL Server 2000 & SQL Server 2005. Not sure about SQL Server 2008, but don’t see why not.
First check if the
name) combination exists in
dbo.syscolumns (an internal SQL Server table that contains field definitions), and if not issue the appropriate
ALTER TABLE query to add it. For example:
IF NOT EXISTS ( SELECT * FROM syscolumns WHERE id = OBJECT_ID('Client') AND name = 'Name' ) ALTER TABLE Client ADD Name VARCHAR(64) NULL
A good friend and colleague of mine showed me how you can also use an
IF block with SQL functions
COLUMNPROPERTY in SQL SERVER 2005+ to check for a column. You can use something similar to the following:
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
Hope you learned something from this post.
Follow Programming Articles for more!