Yesterday I wrote about SQL SERVER – Find Table in Every Database of SQL Server. Today we will see another method how we can achieve the same result using Information_Schema view. Refer my previous article here for additional information.
CREATE PROCEDURE usp_FindTableNameInAllDatabase
@TableName VARCHAR(256)
AS
DECLARE @DBName VARCHAR(256)
DECLARE @varSQL VARCHAR(512)
DECLARE @getDBName CURSOR
SET @getDBName = CURSOR FOR
SELECT name
FROM sys.databases
CREATE TABLE #TmpTable (TABLE_CATALOG VARCHAR(128), TABLE_SCHEMA VARCHAR(128), TABLE_NAME VARCHAR(256), TABLE_TYPE VARCHAR(10))
OPEN @getDBName
FETCH NEXT
FROM @getDBName INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @varSQL = 'USE ' + @DBName + ';
INSERT INTO #TmpTable
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE ''%' + @TableName + '%'''
EXEC (@varSQL)
FETCH NEXT
FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable
DROP TABLE #TmpTable
GO
EXEC usp_FindTableNameInAllDatabase 'Address'
GO
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
2 Comments. Leave new
There is no body better than you sir!!!
Yes. It is better to avoid undocumented procedures
The same can be donw with the help of While loop or concatenation