SQL Server: Finding a column in multiple databases.

My client has many instances of the same database in various versions. The version I’m developing against is missing a field expected by a c# class, and I can’t find a script to create the column.

I came up with the following SQL Server query to allow me to search all the instances of the database to find one that contained the field.

EXEC sp_MSforeachdb 
 'SELECT ''Found in ?''
 FROM sys.columns 
 WHERE Name = N''FieldName''
 AND Object_ID = Object_ID(N''TableName'')'

Replace the field name and the table name with the values you’re looking for and it will search all the databases on the server for  a match.

Advertisements