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

Question

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

User · Answer

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

User · Answer

Only checks 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 EXISTS IN TABLE END

User · Answer

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 EXISTS IN TABLE END

User · Answer

select distinct object name sc id  from syscolumns sc sysobjects so   where sc name like   col name   and so type  U

User · Answer

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

User · 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   Martin Smith s version is shorter   IF COL LENGTH  schemaName tableName    columnName   IS NOT NULL BEGIN     -- Column Exists END

User · Answer

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  amp  SQL Server 2005  Not sure about SQL Server 2008  but don t see why not

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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    lt YourTableName gt  AND COLUMN NAME    lt YourColumnName gt     BEGIN     SELECT  Column Already Exists     END   ELSE   BEGIN     ALTER TABLE  lt YourTableName gt  ADD  lt YourColumnName gt   lt DataType gt  Size    END

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Do something if column is not existing   BEGIN     IF  COL LENGTH   dbo   Table     Column    IS NULL      BEGIN           Do something     END END    Do something if column is existing   BEGIN     IF  COL LENGTH   dbo   Table     Column    IS NOT NULL      BEGIN           Do something     END END

User · Answer

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

User · Answer

if exists     select      from INFORMATION SCHEMA COLUMNS    where TABLE NAME     lt table name gt      and COLUMN NAME     lt column name gt     begin   print  Column you have specified exists  end else begin   print  Column does not exist  end

User · Answer

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

User · Answer

Another contribute is the following sample that add the column if 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   Hope it helps  Simone

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Yet another variation     SELECT    Count    AS existFlag  FROM    sys columns  WHERE     name    N  ColumnName     AND  object id    OBJECT ID N  TableName

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

table -- script table as -- new windows - you have design script  check and find column name in new windows

User · Answer

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 IF wrappers ALTER TABLE Table name DROP COLUMN IF EXISTS Column name

User · Answer

IF EXISTS SELECT 1 FROM sys columns        WHERE Name   N columnName        AND Object ID   Object ID N schemaName tableName         This should be the fairly easier way and straight forward solution to this problem  I have used this multiple times for similar scenarios  It works like a charm  no doubts on that

User · Answer

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

[sql-server] How to check if a column exists in a SQL Server table?

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to sql-server-2012

Examples related to sql-server-2016