Technical Article

Search for a Column in all databases

,

Usage of this script:

If the exact Column name is known then specify the Column name to the stored procedure else include the wild cards

EXEC FindColumn 'EmployeeID'
EXEC FindColumn '%Employee%'

Create Proc FindColumn 
@ColumnName nVarchar(50)
As
/*
Purpose : Search for a Column in all databases
Author: Sandesh Segu
Date: 17th July 2009
Version: 1.0
*/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

Rate

3.8 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

3.8 (5)

You rated this post out of 5. Change rating