Home How to check if a column exists in a SQL Server table?
Reply: 25

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

Maciej
1#
Maciej Published in 2008-09-25 12:34:00Z

I need to add a specific column if it is does not exist. I have something like this, 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 SQL Server database?

Mitch Wheat
2#
Mitch Wheat Reply to 2017-04-12 03:35:20Z

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
Soner Gönül
3#
Soner Gönül Reply to 2011-11-14 07:18:51Z

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
Luke Bennett
4#
Luke Bennett Reply to 2014-12-31 08:57:33Z

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?

shA.t
5#
shA.t Reply to 2015-06-15 12:29:34Z

First check if the table/column(id/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
shA.t
6#
shA.t Reply to 2008-09-25 12:38:35Z

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.

Christian Hayter
7#
Christian Hayter Reply to 2009-06-26 08:58:19Z

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.

Tuomo Kämäräinen
8#
Tuomo Kämäräinen Reply to 2011-03-03 15:49:31Z
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
Martin Smith
9#
Martin Smith Reply to 2014-09-08 19:40:22Z

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

COL_LENGTH('AdventureWorks2012.HumanResources.Department','ModifiedDate')

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.

Joe M
10#
Joe M Reply to 2017-03-23 19:31:12Z

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
Pரதீப்
11#
Pரதீப் Reply to 2014-12-21 14:35:54Z

Try this

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') 
Isaac Lem
12#
Isaac Lem Reply to 2017-03-16 07:30:10Z

I needed similar for SQL SERVER 2000 and, as @Mitch points out, this only works inm 2005+.

Should it help anyone else, this is what worked for me in the end:

if exists (
    select * 
    from 
        sysobjects, syscolumns 
    where 
        sysobjects.id = syscolumns.id 
        and sysobjects.name = 'table' 
        and syscolumns.name = 'column')
brazilianldsjaguar
13#
brazilianldsjaguar Reply to 2013-03-21 17:27:42Z

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
Uwe Keim
14#
Uwe Keim Reply to 2013-12-06 15:27:40Z
select distinct object_name(sc.id)
from syscolumns sc,sysobjects so  
where sc.name like '%col_name%' and so.type='U'
Nunser
15#
Nunser Reply to 2013-09-12 12:47:24Z
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;
JOBG
16#
JOBG Reply to 2016-04-29 23:44:47Z
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
Manuel Alves
17#
Manuel Alves Reply to 2014-07-10 11:01:43Z

Yet another variation...

SELECT Count(*) AS existFlag FROM sys.columns 
WHERE [name] = N'ColumnName' AND [object_id] = OBJECT_ID(N'TableName')
Community
18#
Community Reply to 2017-05-23 11:55:01Z

A temp table version of the accepted answer:

if (exists(select 1 
             from tempdb.sys.columns  
            where Name = 'columnName'
              and Object_ID = object_id('tempdb..#tableName')))
begin
...
end
Daniel Barbalace
19#
Daniel Barbalace Reply to 2015-03-26 17:42:36Z

Wheat's answer is good, but assumes you do not have any identical table name / column name pairs in any schema or database. To make it safe for that condition use this...

select *
from Information_Schema.Columns
where Table_Catalog = 'DatabaseName'
  and Table_Schema = 'SchemaName'
  and Table_Name = 'TableName'
  and Column_Name = 'ColumnName'
Nilesh
20#
Nilesh Reply to 2017-12-13 15:03:47Z

I would best go for

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

This check if the table exists as well as the column in the particular table exists. Tried only on MYSQL though, didn't try it for other databases.

A bit different in the Oracle database.

Select count(*)
    from user_tab_cols
    where column_name = 'myColumnName'
      and table_name = 'myTableName';

This would return you either 1 if a column exists in a particular table, 0 if not present. More of a PL/SQL thing though.

Ali Elmi
21#
Ali Elmi Reply to 2016-02-15 20:45:22Z

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:

 sys.objects
 sys.columns

and even some other access methods available to check system catalog.

Also, no need to use SELECT *, simply test it by NULL value

IF EXISTS(
           SELECT NULL 
           FROM INFORMATION_SCHEMA.COLUMNS
           WHERE
             TABLE_NAME = 'myTableName'
             AND COLUMN_NAME = 'myColumnName'
         ) 
Pரதீப்
22#
Pரதீப் Reply to 2016-05-01 04:46:00Z

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 IF wrappers

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name
Muhammad Muazzam
23#
Muhammad Muazzam Reply to 2016-05-04 13:22:28Z

Use this query:

 IF EXISTS(SELECT Statement) 
    BEGIN
    Code
    END

    ELSE

    BEGIN
    Code
    END

    OR 

    IF NOT EXISTS( Select Statement)
    Begin
    code
    End

    ELSE
    Begin
    code
    End

This way you can use the conditions in SQL.

Hope it helps.

user3070485
24#
user3070485 Reply to 2017-05-18 10:26:50Z

The commercial product I am working with uses a query similar to the one below, checking for the existence of all the required columns at once. The query and in particular the list of columns is created in the software.

SELECT COUNT(Column1+Column2+...+ColumnN)
FROM [Tablename]
WHERE ID=NULL

The query is presented to the server via software and an ODBC link to the database and is run for our C++ program within a try-catch exception handler.

If the query runs okay, the columns exist. If the query fails, then further queries are executed and the missing columns created etc, so that our software always has the tables and columns it needs to run correctly. Older databases are updated to add the columns they are missing.

The solution differs from many solutions above since it is running from software and it can handle database servers which predate certain SQL server systems tables and functions like INFORMATION_SCHEMA.COLUMNS, sys.columns and object_id() some of which are vendor specific anyway and do not provide a general solution across Microsoft SQL server and Adobe MySQL products etc.

If your queries are run by your own software and an ODBC connection to the SQL Server, and you want them to run with databases other than SQL Server too, then this approach might be worth consideration.

This post has been down-voted numerous times. I know its not a solution for most people since it is for integration within software, and I know it's not fashionable to provide something that works with legacy systems rather than assuming and relying on up to date technology. The try-catch idea is ugly and the use of WHERE ID=NULL to return a single value regardless of actual table contents is not straight forward either. But despite the down votes I am leaving this here in case it helps someone.

Brien
25#
Brien Reply to 2018-01-28 04:41:28Z

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 TableName

Arsman Ahmad
26#
Arsman Ahmad Reply to 2017-05-24 07:17:40Z

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
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.4716 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO