SQL Server – Find if Column Exists in Certain Tables

sql servert-sql

I want to find out all tables having a column let's say test. How to obtain this? I need DBName, TableName, testcolumnexist(Yes/No), TableCreateDate and ProductName details.

The tricky part is tablenames. I have tables with IDs appended at the end. Below is the entire scenario:

  • bColumn to find = test
  • Tables = Logs#, Details#, Data#

Where # represents IDs coming from different table master. i.e. select id from master where id< 5).

So my tables are like Log1, Details1, Data1 for ID = 1, Log2, Details2, Data2 for ID = 2 and so on

Output: DBName, TableName, testcolumnexist(Yes/No), TableCreateDate, ProductName

Note that ProductName column exist in master table.

Best Answer

This will give you the Schema and Table names along with YES or NO and Creation date:

SELECT s.name As [Schema]
    , t.name as [Table]
    , CASE WHEN c.name IS NULL THEN 'NO' ELSE 'YES' END as 'Exists'
    , t.create_date
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN sys.columns c ON c.object_id = t.object_id AND c.name = 'test'

How the master table works is unclear but it seems that adding such a WHERE clause should work:

WHERE t.name IN (
    SELECT tn.name + CAST(mn.id as varchar(10)) as name
    FROM (values('Logs'), ('Details'), ('Data')) as tn(name)
    CROSS JOIN [master] mn
)

This will mix the list of name with the list of ids from master. Names can be added to the list or it could be replaced by a Names table.

By the way, master is not a proper name for a table since it is also a system database name.

This query adds productname from sample @master table for id < 5:

declare @master table (id int, productname varchar(10))
insert into @master(id, productname) values(1, 'xxx'), (2, 'yyy'), (6, 'yyy')

SELECT s.name As [Schema]
    , t.name as [Table]
    , CASE WHEN c.name IS NULL THEN 'NO' ELSE 'YES' END as 'Exists'
    , t.create_date
    , m.productname
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN (
    SELECT name = tn.name + CAST(mn.id as varchar(10)) 
        , mn.productname
    FROM (values('Logs'), ('Details'), ('Data')) as tn(name)
    CROSS JOIN @master mn
    WHERE mn.id < 5
) m ON m.name = t.name
LEFT JOIN sys.columns c ON c.object_id = t.object_id AND c.name = 'test'
Related Question