How to test for existence of a specific set of fields in a table

Question

I want to be able to test if a table has a specific set of fields (I'll settle for name comparison only; of course, types/lengths/etc could have been changed). I see that testing for existence of individual fields is not that difficult, but the table I am dealing with has about 80 fields, and I am hoping to not have to go through each one.

The main goal here is to perform a sanity check in code, to confirm the table hasn't been changed, so when records are copied, no fields are missed.

http://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server — Blood-HaZaRd, Feb 05 '15 at 16:57

score 0 · Accepted Answer · answered Feb 05 '15 at 16:58

0

I believe your query should be

SELECT count(*)
 FROM DatabaseName.INFORMATION_SCHEMA.COLUMNS
 WHERE TABLE_NAME = N'TableName' and COLUMN_NAME='YourColumnName'

If count>0 then it Exist

answered Feb 05 '15 at 16:58

MDMalik

3,951
2
25
39

I think this should do it, with some minor mods. I'll change ``COLUMN_NAME='YourColumnName'`` to ``COLUMN_NAME IN (...)`` to get all the columns, test that the final result is indeed equal to that number, and also test for the absolute number of columns in the table. – Conrad Feb 05 '15 at 18:14

Dudi Konfino · Answer 2 · 2015-02-05T18:37:41.183

0

if
(SELECT ORDINAL_POSITION 
FROM information_schema.columns
WHERE TABLE_CATALOG='your_db' 
AND 
TABLE_NAME='YOUR_TABLE' 
AND 
COLUMN_NAME='COLUMN_NAME')>0
..

edited Feb 05 '15 at 18:37

answered Feb 05 '15 at 18:19

Dudi Konfino

1,126
2
13
24

How to test for existence of a specific set of fields in a table

2 Answers2