Find what database and object a column belongs to

>>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 =”‘[email protected]+'” 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 =”‘[email protected]+'” 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

Latest Articles