Musings on code and life

Find Table Query

I was looking for some sql that would make it easy to find a sql table matching on part of the name, database, or schema.


--REF: http://stackoverflow.com/questions/2875768/how-do-i-list-all-tables-in-all-databases-in-sql-server-in-a-single-result-set

DECLARE @SearchDb nvarchar(200) = '%'
DECLARE @SearchSchema nvarchar(200) = '%'
DECLARE @SearchTable nvarchar(200) = '%'

SET NOCOUNT ON

DECLARE @AllTables TAble (DbName sysname, SchemaName sysname, TableName sysname)
DECLARE @SQL nvarchar(4000) = '
USE [?]
IF DB_ID(''?'') > 4 BEGIN --user db only
SELECT ''?'' as DbName, s.name as SchemaName, t.name as TableName 
FROM [?].sys.tables t 
INNER JOIN sys.schemas s on t.schema_id=s.schema_id 
WHERE ''?'' LIKE '''+@SearchDb+''' 
AND s.name LIKE '''+@SearchSchema+''' 
AND t.name LIKE '''+@SearchTable+'''
END'

INSERT INTO @AllTables (DbName, SchemaName, TableName)
EXEC sp_msforeachdb @SQL

SET NOCOUNT OFF

SELECT * FROM @AllTables ORDER BY DbName, SchemaName, TableName