How do I check if a column exists in SQL Server?
36,351
Solution 1
IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='tablename' AND COLUMN_NAME='columname' )
Solution 2
If col_length('table_name','column_name') is null
select 0 as Present
ELSE
select 1 as Present
Present will be 0, is there is no column_name present in table_name , otherwise 1
@CMS: I don't think that 'INFORMATION_SCHEMA.COLUMNS' have information about every table in DB. Because this didn't worked for me. But my answer did worked.
Solution 3
In query analyzer, select the Database that contains the table in which you need to check if the field exists or not and run the query below.
SELECT count(*) AS [Column Exists]
FROM SYSOBJECTS
INNER JOIN SYSCOLUMNS ON SYSOBJECTS.ID = SYSCOLUMNS.ID
WHERE
SYSOBJECTS.NAME = 'myTable'
AND SYSCOLUMNS.NAME = 'Myfield'
Solution 4
This should do nicely:
if COLUMNPROPERTY(object_id('table_name'), 'column_name', 'ColumnId') is null
print 'doesn\'t exist'
else
print 'exists'
Author by
Admin
Updated on October 16, 2020Comments
-
Admin over 3 years
How do I check if a column exists in SQL Server 2000?