How to check if column exists in SQL Server table

Question

I need to add a specific column if it 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 on a table in SQL Server?

Answer 1

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

Answer 2

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?

Answer 3

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

Answer 4

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.

Answer 5

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

Ity should work on both SS2000 & SS2005. Not sure about 2008, but don't see why not.

Answer 6

SQL Server 2005 onwards:

if Exists(select * from sys.columns where Name = N'columnName'  
            and Object_ID = Object_ID(N'tableName'))

begin

    -- Column Exists

end

Answer 7

if Exists(select * from sys.columns where Name = N'columnName' and Object_ID = Object_ID(N'tableName'))begin-- Column Existsend

Answer 8

why don't u try like query with tabl_ename and column_name?

Answer 9

how to found column exist or not in sql script file (.sql)

declare

count_int number(2); begin select count(*) into count_int from ALL_TAB_COLUMNS cols where cols.TABLE_NAME='Name_of_table' and cols.COLUMN_NAME='name_of_column_to_find'; if(count_int > 0) then dbms_output.put_line('INFO: name_of_column_name_to_find exist in Name_of_table '); else EXECUTE IMMEDIATE 'ALTER TABLE Name_of_table ADD name_of_column_to_find datatype'; end if;

end; /

Answer 10

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.