How to check whether the table contains a particular column or not?

Question

Look here: http://stackoverflow.com/questions/600446/sql-server-how-do-you-return-the-column-names-from-a-table — user_0, Jun 04 '15 at 13:49
information_schema.columns! (Or try if select column from table raises an error or not...) — jarlh, Jun 04 '15 at 13:49
Possible duplicate of [How do you return the column names of a table?](https://stackoverflow.com/questions/600446/how-do-you-return-the-column-names-of-a-table) — Brian Tompsett - 汤莱恩, Mar 18 '18 at 17:56

score 3 · Answer 1 · answered Jun 04 '15 at 13:50

3

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'table_name' AND COLUMN_NAME = 'column_name'

answered Jun 04 '15 at 13:50

Code Different

90,614
16
144
163

score 2 · Answer 2 · answered Jun 04 '15 at 13:52

You can query the information schema tables for this kind of information and much more.

In your case something like this would be useful:

select
* 
from
INFORMATION_SCHEMA.COLUMNS
where
    table_schema = '<your schema>'
    and
    table_name = '<your table>'

score 1 · Answer 3 · answered Jun 04 '15 at 14:02

1

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

answered Jun 04 '15 at 14:02

Rahul Sharma

453
3
10

score 1 · Answer 4 · answered Jun 04 '15 at 15:34

1

Since you are looking for a particular column.

IF EXISTS(
SELECT TOP 1 *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = 'TableName'
AND [COLUMN_NAME] = 'ColumnName'
AND [TABLE_SCHEMA] = 'SchemaName')
BEGIN
PRINT 'Your Column Exists'
END

answered Jun 04 '15 at 15:34

HKB

55
1
6

How to check whether the table contains a particular column or not?

4 Answers4