1

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.

1 Answer 1

3

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' 
5
  • You anticipated the master perfectly. You are a genius! Thanks for that! I have one more addition to this. I need one column lets say productname from master table in final select. How to add that?
    – SQLPRODDBA
    Mar 3, 2016 at 9:25
  • Updated the question.
    – SQLPRODDBA
    Mar 3, 2016 at 9:36
  • and answer now.... Mar 3, 2016 at 9:39
  • Awesome Julien Vavasseur! I want to vote you 10 more times but there is no such option here. :)
    – SQLPRODDBA
    Mar 3, 2016 at 9:46
  • Thanks. Giving more than 10 is called a bounty: dba.stackexchange.com/help/privileges/set-bounties Mar 3, 2016 at 9:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.