Search/Find a table in all the Database

Niladri.biswas
Posted by Niladri.biswas under Sql Server category on | Points: 40 | Views : 1404
This query will help us to find a table in all the available database

DECLARE @tblNameToSearch VARCHAR(100) = 'test'

DECLARE @t TABLE (SchemaName SYSNAME,DBName SYSNAME, TableName SYSNAME)
INSERT INTO @t (SchemaName,DBName,TableName)
EXEC sp_msforeachdb 'SELECT
s.name AS SchemaName
,''?'' AS DbName
, t.name AS TableName
FROM [?].sys.tables t
JOIN sys.schemas s
ON t.schema_id=s.schema_id'

SELECT * FROM @t
WHERE TableName LIKE '%' + @tblNameToSearch + '%'

Comments or Responses

Login to post response