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'