whether a column exists in my table?(Urgent)

Hi

Can anybody tell me how can I find whether a column exists in my table or not???

Piyush

well, since it's urgent...

SELECT TOP 1 * FROM yourtable

Thats fine,now what when it is not so urgent but required?????

sorry, please clarify your question

to determine whether a particular column exists in the table, run my query, and look at the output

Do you mean programmatically checking MsSQL's system tables to see what columns make a table?

programmatically, should not the result row be structured using the names of the columns?

that will not give details like datatype, length, precision, nulls, defaults, or keys

but the original requirement was "exists" so scanning the column names of a one-row query should do it, no?

Yes, scanning columns names works, mine was just an hypothesis.

What I was looking for was a front end query that cud give me whether a particular column exists or not , may be a boolean return,surely i am not looking for as detailed info as sp_help table returns.

in that case, use INFORMATION_SCHEMA.COLUMNS

this is the best way

the only reason i said to use TOP 1 was because (a) you did not mention that you wanted a programmatic solution, and (b) you said it was urgent

now, since it has become clear that your problem is programmatic and is not urgent, i would recommend the correct way

How about the following

SELECT COUNT(*)
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID('TableName')
AND Name = 'ColumnName'

If you get a count of > 0 then your column exists.

Information schema views are better because "These components constitute a published API for obtaining system information from SQL Server. Microsoft maintains the compatibility of these components from release to release. The format of the system tables is dependent upon the internal architecture of SQL Server and may change from release to release." (see System Tables)

Excuse me.
I can to find all the table which have a ColumName known ?

Something as:
SELECT TableName
FROM ???
WHERE COLUMN_NAME = 'ColumnName'

I want to know all the tables which contains a column with name = 'CITY'

Thanks

nicola65mi, have you tried the INFORMATION_SCHEMA views?