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