Find Table Or Stored Procedure in all Databases on a Server

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.

[code language="sql"]
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
[/code]

following is how the out put would look like: