Technical Article

Search for a Table in all databases

,

Usage of this script:

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

EXEC FindTable 'Employee'
EXEC FindTable '%Employee%'

Create Proc FindTable 
@TableName nVarchar(50)
As
/*
Purpose : Search for a Table 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))

Declare @SQL Varchar(500)
Set @SQL='Use [?] ;
if exists(Select name from sys.tables where name like '''+@TableName+''') 
insert into #temp 
Select ''?'' AS DatabaseName ,SS.Name AS SchemaName ,ST.Name AS TableName from sys.tables as ST , sys.schemas SS 
where ST.Schema_ID=SS.Schema_ID and ST.name like '''+@TableName+''''

EXEC sp_msforeachdb @SQL

Select * from #temp

Drop table #temp
GO

Rate

4 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (3)

You rated this post out of 5. Change rating