query How to check if a column exists in a SQL Server table?
sql query to check if column exists in database (20)
Here is a simple script I use to manage addition of columns in the database:
IF NOT EXISTS ( SELECT * FROM sys.Columns WHERE Name = N'QbId' AND Object_Id = Object_Id(N'Driver') ) BEGIN ALTER TABLE Driver ADD QbId NVARCHAR(20) NULL END ELSE BEGIN PRINT 'QbId is already added on Driver' END
In this example, the
Name is the
ColumnName to be added and
Object_Id is the
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
For the people who is checking the column existence to drop it.
In SQL Server 2016 you can use new DIE statements instead of big
ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name
Below query can be used to check whether searched column exists or not in the table. We can take decision based on the searched result also as shown below.
IF EXISTS (SELECT 'Y' FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = <YourTableName> AND COLUMN_NAME = <YourColumnName>) BEGIN SELECT 'Column Already Exists.' END ELSE BEGIN ALTER TABLE <YourTableName> ADD <YourColumnName> <DataType>[Size] END
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' +' ['[email protected]+'] bigint null') end
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
SELECT COLUMNS.* FROM INFORMATION_SCHEMA.COLUMNS COLUMNS, INFORMATION_SCHEMA.TABLES TABLES WHERE COLUMNS.TABLE_NAME = TABLES.TABLE_NAME AND Upper(COLUMNS.COLUMN_NAME) = Upper('column_name')
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
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.
select distinct object_name(sc.id) from syscolumns sc,sysobjects so where sc.name like '%col_name%' and so.type='U'
One of the most simple and understandable solution is:
IF COL_LENGTH('Table_Name','Column_Name') IS NULL BEGIN -- Column Not Exists, implement your logic END ELSE BEGIN -- Column Exists, implement your logic END
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.
There are several ways to check the existence of a column.
I would strongly recommend to use
INFORMATION_SCHEMA.COLUMNS as it is created in order to communicate with user.
Consider following tables:
and even some other access methods available to check
Also, no need to use
SELECT *, simply test it by
IF EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName' )
IF NOT EXISTS( SELECT NULL FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tablename' AND table_schema = 'db_name' AND column_name = 'columnname') THEN ALTER TABLE `TableName` ADD `ColumnName` int(1) NOT NULL default '0'; END IF;
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
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
This worked for me in SQL 2000:
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'table_name' AND column_name = 'column_name' ) BEGIN ... END
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.
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?