I need a query to find list of tables which is not having a column name in it. Like column name is source.
Thanks in advance, Rohit
Probably the easiest way is to use INFORMARTION_SCHEMA.Columns, it works on the database that you're currently connected to.
SELECT c.TABLE_CATALOG, c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.ORDINAL_POSITION, c.COLUMN_DEFAULT, c.IS_NULLABLE, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH, c.CHARACTER_OCTET_LENGTH, c.NUMERIC_PRECISION, c.NUMERIC_PRECISION_RADIX, c.NUMERIC_SCALE, c.DATETIME_PRECISION, c.CHARACTER_SET_CATALOG, c.CHARACTER_SET_SCHEMA, c.CHARACTER_SET_NAME, c.COLLATION_CATALOG, c.COLLATION_SCHEMA, c.COLLATION_NAME, c.DOMAIN_CATALOG, c.DOMAIN_SCHEMA, c.DOMAIN_NAME FROM INFORMATION_SCHEMA.COLUMNS AS c WHERE c.COLUMN_NAME NOT LIKE '%YourColumnName%' --WHERE c.COLUMN_NAME <> 'YourColumnName' --WHERE c.COLUMN_NAME NOT IN ('YourColumn1','YourColumn2')
Hope that helps.
23 People are following this question.