How can I check if column exists?

[vladibo]

How can I check inside a T-SQL script if column exists?

 

[vongrunt]

Through system tables or information_schema view:

if exists( select * from sysobjects A inner join syscolumns B on A.id=B.id where A.name='table_name' and B.name='column_name')
	print ('exists')

if exists( select * from information_schema.columns where table_name = 'table_name' and column_name='column_name' )
	print ('exists')

 

[vladibo]

Thank you

 

[rockraines]

Here is a piece of code that will allow you to specify a column that you are looking for and tell you all the tables that the column appers in.

CREATE PROCEDURE [spColumnsInTableNew] @columnName varchar(50)

AS

select sc.name as ColumnName, so.name as TableName
from syscolumns as SC inner join sysobjects as SO on SC.id = SO.id
where sc.name = @columnName
AND so.xtype = 'U'
Order By so.name


Ex SpColumnsInTableNew 'FirstName' (this will return every table that the name of the column 'FirstName' exists.