SQL Server queries which tables a field exists in

--All tables containing the hospital numbered field

Select A.[name] from sysobjects A,

(

SELECT [Id],count (*) b from syscolumns

where [name] = ' Column1 '

GROUP by [ID]

)

b where a.[id]=b.[id] ORDER by a.name ASC

--All tables containing the hospital number and the Department number field

Select A.[name] from sysobjects a

Left Join

(

SELECT [Id],count (*) b from syscolumns where [name]

In (' Column1 ', ' Column2 ') group by [ID] has count (*) >1

) b

On A.[id]=b.[id]

Where b.id is not null