SQLTeam.com | Weblogs | Forums

Show all tables where two columns exist - SQL 2008

I want to a search for all tables that includes both of 'Documentversionid' and 'ClientMedicationID' columns.

Thank You,

Helal

select  object_name(c.object_id)
from    sys.columns c
where   c.name    in ('Documentversionid', 'ClientMedicationID')
group by object_name(c.object_id)
having  count(*) = 2
1 Like

Thank you. Worked like a charm

Another option:

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name in ('Documentversionid', 'ClientMedicationID' )
ORDER BY schema_name, table_name;

perhaps I haven;t looked at it closely enough ... !! but won't that get you any table that includes EITHER of the column names but not necessary BOTH of them?

Right you are. Thanks for the catch.