How to query all the tables in sql server for a specific column? 1

[jsql12]

Hi all,

is there a query that I can use to query all the tables in sql server 2005 for a specific column called status in which there's a value like '%out of state%'?

Any help is greatly appreciated.

Thanks

 

[AndyKr]

I don't know of any way to query all tables like this.

You can easily find where such columns are located using a query like this:

SELECT SY.name
FROM sysobjects SY, syscolumns SC
WHERE SY.id = SC.iD
AND SY.type = 'U'
AND SC.Name = 'status'

Then you would need to generate a query for each row in the result set.

----
Andy Kramek
Visual FoxPro MVP

 

[bborissov]

jsql12,
Is this a one time job or you need this daily?
Also what you mean with ALL table in SQL Server? Even if the DataBase is not your? Scan even System DBs and any other DBs installed?
If this is an one time job you can use sp_MSForEachTable SP.

sp_MSForEachTable 'SELECT ''?'' AS TableName,* FROM ? WHERE status LIKE ''%out of state%'''

It will bombs for these table that doesn't have such field but you will get the results.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[jsql12]

Thank you AndyKr and bborissov a lot!

I need to do this just one time. I'll try both solutions and see how it goes.

Thanks again!

 

[AlexCuse]

As an alternative, this could work for you with a slight modification thread183-1298424

This has the benefit of giving you table and column names where your value shows up (rather than needing to go through result sets, identify what table they came from, etc...)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[jsql12]

Thank you all for your help. You were extremly helpful

jsql12