How to Check for existence of a field across multiple tables 1

[timgill]

Let's say you have a database that has 100 tables in it. I want to be able to search across all tables and return a list of all tables where a specific field exists.

Any ideas on a nice, clean way to do this?

Tim Gill
Gill Consulting

http://www.dagills.com

 

[Andel]

Tim, try this...

declare @myfield varchar(50)
set @myfield = 'i_account_id' -- the specific field
select o.name as tablename, c.name as colname
from syscolumns c, sysobjects o
where c.id = o.id and c.name = @myfield

Hope this helps..

Maingel