Here is a Stored Procedure which scans all your databases for the Column which you are searching for.
It might be easy to find a Column in a server which has less databases, but it is a bit difficult when there are more number of databases and it is also time consuming to do it manually. So to avoid the manual job, use the below stored procedure FindColumn. This will search for the given Column in all databases and provides the result with the schema name, Table Name and the database name in which it is present.
This Code is tested for SQL Server 2005 and SQL server 2008.
Code:
Create Proc FindColumn
@ColumnName nVarchar(50)
As
/*
Purpose : Search a Column in all databases
Author : Sandesh Segu
Date : 17th July 2009
Version : 1.0
More Scripts : http://sanssql.blogspot.com
*/
Create Table #temp (DatabaseName varchar(50),SchemaName varchar(50),TableName varchar(50),ColumnName varchar(50))
Declare @SQL Varchar(500)
Set @SQL='Use [?] ;
insert into #temp
Select ''?'' AS DatabaseName ,SS.Name as SchemaName ,ST.Name AS TableName ,SC.Name AS ColumnName from sys.tables ST ,sys.columns SC ,sys.schemas SS where SC.object_id=ST.object_id and ST.schema_id=SS.schema_id and SC.name like '''+@ColumnName+''''
EXEC sp_msforeachdb @SQL
Select * from #temp
Drop table #temp
GO
/*
Usage: If the exact table name is known then specify the table name else include the wild cards
*/
EXEC FindColumn 'EmployeeID'
EXEC FindTable '%Employee%'
Wednesday, September 30, 2009
Search for a Column in all databases
Labels:
Interview Questions,
MSSQL,
SQL Information,
SQL Queries
Subscribe to:
Post Comments (Atom)
2 comments:
You should use the INFORMATION_SCHEMA.Tables and INFORMATION_SCHEMA.Columns views instead of the system tables for determining this.
-Eric Isaacs
Hello Eric,
If you see the text of INFORMATION_SCHEMA.Tables and INFORMATION_SCHEMA.Columns views, these are also quering the system tables. So if you use INFORMATION_SCHEMA.Tables and INFORMATION_SCHEMA.Columns, you are indirectly calling the system tables.
Regards,
Sandesh Segu
Post a Comment