Searching for columns... 1

[kizmar2]

I need to search multiple (over 200) tables for information. At the moment I'm having to search through the tables until I find a certain column name, then I search that column for certain values.

Is there a way to shorten this process, or at least figure out which tables have this column? I ran a query on the syscolumns table and it looks like there's over 180 tables that have this column in it.

When I find a table with the column I'm looking for, I do this:

SELECT *
FROM <table_name>
WHERE TermCalendarID in (1,2,3,4,5,51,53,138,133)

It would be nice to be able to do this without having to look through a couple hundread tables.

KizMar
------------

http://www.maximizetech.com

 

[gmmastros]

Not perfect, but try this...

Select 'Select * From [' + Table_Name + '] WHERE TermCalendarID in (1,2,3,4,5,51,53,138,133)'
From   Information_Schema.Columns 
Where  Column_Name = 'TermCalendarID'

When you run this in Query Analyzer, you will see a bunch of select strings in the output window. Copy/paste from the output window to the query window and run it.

To get a better idea how this query works...

Select * from information_schema.columns

and

Select * From Information_Schema.Tables

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

DECLARE @lcSql varchar(5000)
SET @lcSql = 'SELECT ''?'' AS Tbl, * FROM ? WHERE TermCalendarID in (1,2,3,4,5,51,53,138,133)'
exec dbo.sp_MSforeachTable @lcSql

You could get a bunch of error messages for these table that has no field named TermCalendarID, but you'll get the results for others. I tried to avoid this but without success :-(



Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[kizmar2]

Thank you, these were very helpfull!

KizMar
------------

http://www.maximizetech.com