>>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