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

Asked 2023-09-20 20:55:24 View 5,124

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?

  • I don't actually think there's anything wrong with the code in the question: Works finely for me in 2008 R2. (Maybe you were running it in the wrong database? Maybe your database was case-sensitive and you didn't have the case right in your myTableName / myColumnName strings? This type of query seems more flexible than the COL_LENGTH solution: I'm able to run it against a different database and even over a database link by suitably prefixing "INFORMATION_SCHEMA". Couldn't see how to do that with the COL_LENGTH metadata-function. - anyone
  • @mwardm - COL_LENGTH('AdventureWorks2012.HumanResources.Department ','ModifiedDate') works fine. - anyone
  • Little related hint: if you want to update a column right after column addition(I believe many users were searching this article for that purpose), you could use EXEC sp_executesql with formed UPDATE statement. - anyone
  • The real answer is you should add the database you are checking against so it's FROM [YourDatabase].INFORMATION_SCHEMA.COLUMNS - anyone
  • You can also use syscolumns and sysobjects very simply. - anyone

Answers

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

Answered   2023-09-20 20:55:24

  • In Martin Smith's version, one thing to mention is not to include columnName within square brackets [ ]. When columnName is inside square brackets [ ], it'll give null even if the column exists in the table - anyone
  • @HemendraSinghChauhan - that's because they aren't part of the name. You will also find that when comparing with the name in sys.columns - anyone
  • @MartinSmith didn't knew that, I was using your answer and came across this. Generally I use square brackets during adding columns, so I used them inside COL_LENGTH function too. My code was like this: Alter table Table_Name Add [ColumnName] NVarchar(max) NULL; Select COL_LENGTH('[TABLE_NAME]', '[COLUMN_NAME]') - anyone
  • @AlejandroDG Can you provide an example? I would say, this claim is not true. - anyone
  • @sur - So in what sense would that mean "AlejandroDG is right"? The predicate is IS NOT NULL not >0 - anyone

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.

Answered   2023-09-20 20:55:24

  • This is less readable than some of the other answers, probably why it's not as highly rated. - anyone
  • @Bill - Less readable in what way? Looks fine in Firefox. This answer was posted more than 2 years later than the accepted one, which explains the rating IMO. If you meant less clear that it is an existence check this type of idiom is quite common in SQL Server. e.g. using IF OBJECT_ID('TableName','U') IS NULL to check object existence or DB_ID('foo') to check database existence. - anyone
  • @MartinSmith I'm sure he meant less readable because if you didn't know this idiom, and you inherited this code from someone else, you would not immediately understand what the code does. Kind of like writing x>>2 instead of x/4 in C++. The more verbose code (if exists (select column_name from information_schema ...)) takes a lot more space, but no one would ever scratch their head trying to figure out what it does. - anyone
  • Besides more concise this is a way faster solution. Accessing INFORMATION_SCHEMA views or sys.columns hits disk, while COL_LENGTH uses cached database metadata. - anyone
  • This is probably not the most highly rated answer because it was given 2.5 years after the other one. That's why I always check the dates when comparing the ratings on two answers. It takes a lot longer to overcome an answer that was given much earlier. ;) - anyone

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

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?

Answered   2023-09-20 20:55:24

  • I just found out that adding TABLE_SCHEMA = 'mySchema' after where clause fixes the problem. - anyone
  • -1: does not answer OP's question, only adds the new information on how to add a new collumn despite OP not asking about that at all, does not address OP's comment. - anyone
  • +1 Answers OP's question perfectly with a bonus of the additional information the OP was going for next anyways. And this was what I was looking for. - anyone

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

Answered   2023-09-20 20:55:24

  • This method also works with SQL CE, whereas some of the other methods mentioned do not. - anyone
  • You can use SELECT 1 instead of SELECT TOP 1 1 ;). - anyone
  • Within an EXISTS statement SQL automatically optimizes the columns away (much like count(*)) so SELECT * will suffice. - anyone
  • For the sake of completeness, you should consider adding and [TABLE_SCHEMA] = '???' to the WHERE clause. - anyone

For the people who are checking the column existence before dropping it.

From SQL Server 2016 you can use new DIE (Drop If Exists) statements instead of big IF wrappers

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name

Answered   2023-09-20 20:55:24

  • But there isn't a "DIE" in there(?). What is the explanation? Preferably, please clarify it by changing the answer, not here in comments (but without "Edit:", "Update:", or similar - the answer should appear as if it was written today). Independent, can you link to documentation? - anyone
  • Is it possible to do the opposite of this? like ALTER TABLE table_name ADD COLUMN IF NOT EXISTS ? - anyone

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 Server 2008, but it's deprecated and could be removed at any time in future.

Answered   2023-09-20 20:55:24

  • "INFORMATION_SCHEMA views could be incomplete since they are not updated for all new features" - anyone
  • Well yes, that goes without saying since INFORMATION_SCHEMA views contain only ANSI-standard metadata. However, that is sufficient for an existence test. - anyone
  • Microsoft says "In future releases of SQL Server, Microsoft may augment the definition of any system catalog view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM sys.catalog_view_name in production code because the number of columns returned might change and break your application." That implies that they won't remove columns or change their order. That's good enough backward compatibility for all but edge cases. - anyone

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.

Answered   2023-09-20 20:55:24

  • This is exactly what the questionnaire is using, he needed to know how to add the column if it did not exist. - anyone

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 and SQL Server 2005. I am not sure about SQL Server 2008, but I don't see why not.

Answered   2023-09-20 20:55:24

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

Answered   2023-09-20 20:55:24

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 and later 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

Answered   2023-09-20 20:55:24

  • And of course, if you are confident that the table exists, you can leave out the first part of the condition and check on COLUMNPROPERTY only. - anyone
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

Answered   2023-09-20 20:55:24

  • An explanation would be in order. E.g., what is the idea/gist? From the Help Center: "...always explain why the solution you're presenting is appropriate and how it works". Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today). - anyone

This worked for me in SQL Server 2000:

IF EXISTS
(
    SELECT *
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_name = 'table_name'
    AND column_name = 'column_name'
)
BEGIN
...
END

Answered   2023-09-20 20:55:24

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') 

Answered   2023-09-20 20:55:24

  • You don't need INFORMATION_SCHEMA.TABLES and you don't filter columns for a specific table, So it sometimes will return more than one row for same column names in separate tables ;). - anyone

I needed something similar for SQL Server 2000 and, as Mitch points out, this only works in SQL Server 2005 or later.

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')

Answered   2023-09-20 20:55:24

IF NOT EXISTS(SELECT NULL
              FROM  INFORMATION_SCHEMA.COLUMNS
              WHERE table_name = 'TableName'
                    AND table_schema = 'SchemaName'
                    AND column_name = 'ColumnName') BEGIN

  ALTER TABLE [SchemaName].[TableName] ADD [ColumnName] int(1) NOT NULL default '0';

END;

Answered   2023-09-20 20:55:24

  • I think you meant table_schema='schema_name'. - anyone

One of the simplest and understandable solutions 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

Answered   2023-09-20 20:55:24

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 exist'
end

Answered   2023-09-20 20:55:24

Do something if the column does not exist:

BEGIN
    IF (COL_LENGTH('[dbo].[Table]', 'Column ') IS NULL)
    BEGIN
        // Do something
    END
END;

Do something if the column does exist:

BEGIN
    IF (COL_LENGTH('[dbo].[Table]', 'Column ') IS NOT NULL)
    BEGIN
        // Do something
    END
END;

Answered   2023-09-20 20:55:24

  • Why is there a space after "Column"? - anyone

A temporary 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

Answered   2023-09-20 20:55:24

  • How is that different from the accepted answer? Would a temp table not work in the accepted answer? - anyone
  • Correct. The accepted answer does not work for temp tables because 'sys.columns' must be specified as 'tempdb.sys.columns' and the table name must be preceeded by 'tempdb..'. - anyone
select distinct object_name(sc.id)
from syscolumns sc,sysobjects so  
where sc.name like '%col_name%' and so.type='U'

Answered   2023-09-20 20:55:24

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'
         ) 

Answered   2023-09-20 20:55:24

  • No matter even if you SELECT * with EXISTS, because when exists is used it doesn't really select all the rows and all the columns, internally it just checks for the existence and not actually checks for all rows and columns - anyone

Wheat's answer is good, but it 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'

Answered   2023-09-20 20:55:24

  • That is how it should be. With an explanation, not just a "try this" answer. - anyone

Another contribution is the following sample that adds the column if it does not exist.

    USE [Northwind]
    GO

    IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS
                    WHERE TABLE_NAME = 'Categories'
                        AND COLUMN_NAME = 'Note')
    BEGIN

    ALTER TABLE Categories ADD Note NVARCHAR(800) NULL

    END
    GO

Answered   2023-09-20 20:55:24

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

Answered   2023-09-20 20:55:24

The below query can be used to check whether searched column exists or not in the table. We can take a 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

Answered   2023-09-20 20:55:24

Yet another variation...

SELECT 
  Count(*) AS existFlag 
FROM 
  sys.columns 
WHERE 
  [name] = N 'ColumnName' 
  AND [object_id] = OBJECT_ID(N 'TableName')

Answered   2023-09-20 20:55:24

  • Variation of what? Another answer? Mike Wheat's? Or independent? - anyone
  • An explanation would be in order. E.g., what is the idea/gist? How is it different from the previous answers? What was it tested on (versions, etc.)? From the Help Center: "...always explain why the solution you're presenting is appropriate and how it works". Please respond by editing (changing) your answer, not here in comments (without "Edit:", "Update:", or similar - the answer should appear as if it was written today). - anyone

You can check multiple columns in SQLDB at once and return a string as status to check if columns exist:

IF EXISTS
        (
          SELECT *
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = 'Table Name'
          AND(COLUMN_NAME = 'column 1'
          or COLUMN_NAME = 'column 2'
          or COLUMN_NAME = 'column 3'
          or COLUMN_NAME = 'column 4')
        )
        SELECT 'Column exists in table' AS[Status];
        ELSE
        SELECT 'Column does not exist in table' AS[Status];

Answered   2023-09-20 20:55:24

INFORMATION_SCHEMA work with current database, you must first call "Use [MyDatabase]"

Answered   2023-09-20 20:55:24

Execute the below query to check if the column exists in the given table:

IF(SELECT COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'TableName' AND COLUMN_NAME = 'ColumnName') IS NOT NULL
PRINT 'Column Exists in the given table';

Answered   2023-09-20 20:55:24

IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = 'Database Name'
and TABLE_SCHEMA = 'Schema Name'
and TABLE_NAME = 'Table Name'
and COLUMN_NAME = 'Column Name'
and DATA_TYPE = 'Column Type') -- Where statement lines can be deleted.

BEGIN
  -- Column exists in table
END

ELSE BEGIN
  -- Column does not exist in table
END

Answered   2023-09-20 20:55:24