SQL SERVER – Find Table in Every Database of SQL Server – Part 2

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

Reference : Pinal Dave (http://blog.SQLAuthority.com)

3 thoughts on “SQL SERVER – Find Table in Every Database of SQL Server – Part 2

  1. Pingback: SQL SERVER – Weekly Series – Memory Lane – #027 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s