SSIS and Sql Server Journey

Personal Notes of Sarabjit Singh and Bhavpreet Singh

Find Table Or Stored Procedure in all Databases on a Server

on April 3, 2013

Inspired by the Pinal Dave’s Post

Following is the SP to find the Stored Procedure or Table in all databases on a given server.

CREATE PROCEDURE usp_FindTableOrStoredProcNameInAllDatabase
@Name VARCHAR(256)
AS
 DECLARE @DBName VARCHAR(256)
 DECLARE @varSQL VARCHAR(512)
 DECLARE @getDBName CURSOR
 SET @getDBName = CURSOR FOR
 SELECT QUOTENAME([name])
 FROM sys.databases
 CREATE TABLE #TmpTable (
 DBName VARCHAR(256),
 SchemaName VARCHAR(256),
 StoredProcTableName VARCHAR(256),
 SpOrTable VARCHAR(10)
 )
 OPEN @getDBName
 FETCH NEXT
 FROM @getDBName INTO @DBName
 WHILE @@FETCH_STATUS = 0
 BEGIN
 SET @varSQL = 'USE ' + @DBName + ';
 INSERT INTO #TmpTable
 SELECT '''+ @DBName + ''' AS DBName,
 SCHEMA_NAME(schema_id) AS SchemaName,
 name AS StoredProcTableName,
 ''Table'' AS SpOrTable
 FROM sys.tables
 WHERE name LIKE ''%' + @Name + '%'''
 EXEC (@varSQL)

 SET @varSQL = 'USE ' + @DBName + ';
 INSERT INTO #TmpTable
 SELECT '''+ @DBName + ''' AS DBName,
 SCHEMA_NAME(schema_id) AS SchemaName,
 name AS StoredProcTableName,
 ''SP'' AS SpOrTable
 FROM sys.procedures
 WHERE name LIKE ''%' + @Name + '%'''
 EXEC (@varSQL)

 FETCH NEXT
 FROM @getDBName INTO @DBName
END
CLOSE @getDBName
DEALLOCATE @getDBName
SELECT *
FROM #TmpTable Order By SpOrTable
DROP TABLE #TmpTable
GO
EXEC usp_FindTableOrStoredProcNameInAllDatabase 'Address'
GO

following is how the out put would look like:

Image

Advertisements

One response to “Find Table Or Stored Procedure in all Databases on a Server

  1. Hi Bhavpreet
    Nice script..
    Thanks

    Imran

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

%d bloggers like this: