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
asked
List of tables in database not having a particular column
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.
Not sure if this was meant to be the solution or just a hint.
As written, it will list all tables with their columns, even those with column "foo" - it will just leave the column "foo" out of the results.
To leave out the whole table if it has column "foo," change the WHERE clause to
where c.table_name not in (
select table_name
from information_schema.columns
where column_name = 'foo')
This would do the trick when table names are not reused across schema names. However, because it ignores table_schema, it could still be inaccurate if table names duplicate with different schema names.
select SCHEMA_NAME (schema_id),
OBJECT_NAME (object_id)
from sys.tables --all user tables
where object_id NOT IN ( select object_id
from sys.columns --tables with a source column
where name = 'source' )
23 People are following this question.
