TSQL to check whether the table has a column with the specified name?

Published 19 September 07 03:51 PM | SQL Master 

Within SQL Server 2005: 

if Exists(

select * from sys.columns where Name = N'<ColumnName>'  and Object_ID = Object_ID(N'<TableName>')

)

begin

--write your own code print relevant code columns for existence

end

else

begin

--write your own code print relevant code columns for non-existence

end

For SQL Server 2000:

SELECT CASE WHEN ISNULL(COLUMN_NAME ,'')='' THEN 'NO' ELSE 'YES' END
FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='VoucherSerial' AND TABLE_NAME='Vouchers'

Comments

# Other SQL Server Blogs around the Web said on September 19, 2007 4:17 PM:

Within SQL Server 2005: if Exists( select * from sys.columns where Name = N &#39;&lt;ColumnName&gt;&#39;

# SSQA.net - SqlServer-QA.net said on September 19, 2007 4:37 PM:

Within SQL Server 2005: if Exists( select * from sys.columns where Name = N &#39;&lt;ColumnName&gt;&#39;

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.