Finding a specific column in multiple databases

[dmando84]

Hello,

I am trying to find a single column that could be in any of my 30+ databases. Does anyone know the syntax id for this. I tried this query, but it returns an error....

select * from information_schema.database.columns where column_name like 'ProductID%'

Any help would be appreciated.

Thank you,

Dave

 

[Catadmin]

Which version of SQL are you using?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[gmmastros]

You can use the undocumented procedure sp_msforeachdb to accomlish this.

Create Table #Temp(DatabaseName VarChar(500), Table_Name VarChar(500))

exec sp_msforeachdb 'Insert Into #Temp Select Table_Catalog, Table_Name
                     From   [?].information_schema.columns 
                     Where  Column_Name = ''[!]ColumnNameToFind[/!]'''

Select * From #Temp
Drop Table #Temp

-George

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

 

[dmando84]

I am using 2000. Why should that matter?

 

[Catadmin]

The system views in SQL 2000 are different than those in SQL 2005. I was going to write a search based on them. However, try George's solution first. If it doesn't work for you, let us know and we can code up a quick system view check for you.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"