Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

SQL Scripts & Samples

Posted Aug 10, 2006

Find what database and object a column belongs to

By DatabaseJournal.com Staff



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



SQL Scripts & Samples Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
SQL Scripts & Samples Forum
Topic By Replies Updated
sql help!! add 1 August 22nd, 11:58 AM
How To Combine These 2 Queries into 1 Query ? tarek_land 1 June 7th, 08:37 AM
solving query svibuk 1 February 3rd, 06:08 AM
converting from a character string to uniqueidentifier saturnius 4 January 4th, 05:56 AM