Search for a Table in all databases | SansSQL

Wednesday, September 30, 2009

Search for a Table in all databases

Here is a Stored Procedure which scans all your databases for the table which you are searching for.
It might be easy to find a table 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 FindTable. This will search for the given table in all databases and provides the result with the schema 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 FindTable
@TableName nVarchar(50)
As
/*
Purpose : Search for a Table 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))

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

/* 
Usage: If the exact table name is known then specify the table name else include the wild cards
*/
EXEC FindTable 'Employee'
EXEC FindTable '%Employee%'

1 comment:

Harish Reddy said...

Hi Sandesh,

Its a good post, if I pass the table name with schema, nothing is returned.

Thanks,

Post a Comment