SQLTeam.com | Weblogs | Forums

Show all tables where two columns exist - SQL 2008

sql2008

#1

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

Thank You,

Helal


#2
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

#3

Thank you. Worked like a charm


#4

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;


#5

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?


#6

Right you are. Thanks for the catch.