Find what database and object a column belongs to

August 10, 2006

>>Script Language and Platform: SQL Server 2000 and SQL Server 2005
This script searches a column on all the databases from a server.

Note: Please update @columnnametosearch with the column name that you are searching. Please choose the right script based on your SQL Server version.

Author: MAK

--If SQL Server 2000

use master
go
set quoted_identifier off
declare @columnnametosearch varchar(128)
declare @query varchar(1000)
set @columnnametosearch ='Firstname'
set @query='set quoted_identifier off begin use [?] end begin 
 select db_name() as databasename,
 object_name(id) as Objectname,
 Name as ColumnName from syscolumns 
 where name ="'+@columnnametosearch+'" end'
--print @query
exec sp_msforeachdb @query

--If SQL Server 2005

use master
go
set quoted_identifier off
declare @columnnametosearch varchar(128)
declare @query varchar(1000)
set @columnnametosearch ='Firstname'
set @query='set quoted_identifier off begin use [?] end begin 
 select db_name() as databasename,
 object_name(object_id) as Objectname,
 Name as ColumnName from sys.columns 
 where name ="'+@columnnametosearch+'" end'
--print @query
exec sp_msforeachdb @query

Disclaimer: We hope that the information on these script pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, or fitness for a particular purpose... Disclaimer Continued

Back to Database Journal Home