List any Object In Any Database On Server

CREATE PROCEDURE spFindObjectUsage
 @ObjectToFindNVARCHAR(100) = ''
,@ResultMessageVARCHAR(200) = '' OUTPUT
AS
DECLARE
 @ReturnCodeINT
,@StringToExecuteNVARCHAR(1500)
,@DBToProcessINT
,@ServerNameVARCHAR(200)
,@TableWithServerVARCHAR(200)
,@NameOfDatabaseVARCHAR(50)
--
DECLARE@ServerDatabaseTables TABLE
(TempTblIDINT NOT NULL IDENTITY(1,1)
,DBNameVARCHAR(100) NOT NULL DEFAULT ''
,ProcessedBIT NOT NULL DEFAULT 0)
--
CREATE TABLE #ServerDatabaseObjectUsage 
(UsageIDINT NOT NULL IDENTITY(1,1)
,DBNameVARCHAR(100) NOT NULL DEFAULT ''
,ObjectUsedInVARCHAR(200) NOT NULL DEFAULT ''
,TypeOfObjectVARCHAR(50)  NOT NULL DEFAULT ''
,IsColumnOfTable BIT NOT NULL DEFAULT 0)
--
-- first get all the databases on the current server
--
INSERT INTO @ServerDatabaseTables
(DBName)
SELECT
name
FROM master.dbo.sysdatabases
--
SET @TableWithServer = ''
SET @NameOfDatabase  = ''
--
SET NOCOUNT ON
-- each database has it's own listing of System Objects so inorder to get
-- a correct listing we will need to go through every database.
-- the only way I know to do this is using sqlexec.
-- I know it is not the best way but we will need the ability to dynamically
-- tell the query what system tables to use. Ex: master.dbo.systemobjects or production.dbo.systemobjects...ect
WHILE EXISTS(SELECT * FROM @ServerDatabaseTables WHERE Processed = 0) BEGIN
SET @DBToProcess = (SELECT MIN(TempTblID) FROM @ServerDatabaseTables WHERE Processed = 0)
--
SELECT   @ServerName   = DBName + '.dbo.'
,@NameOfDatabase  = DBName
FROM @ServerDatabaseTables
WHERE TempTblID = @DBToProcess
--
SET @StringToExecute =  'INSERT INTO #ServerDatabaseObjectUsage ' +
        '(DBName' +
',ObjectUsedIn' +
',TypeOfObject' + 
',IsColumnOfTable) ' +
'SELECT DISTINCT ' +
char(39)+@NameOfDatabase+char(39)+ 
',obj.NAME' + 
',(CASE obj.XTYPE  WHEN ' + char(39) + 'P' + char(39) + 
 ' THEN ' + char(39) + 'PROCEDURE' + char(39) +
 ' WHEN ' + char(39) + 'V' + char(39) + 
 ' THEN ' + char(39) + 'VIEW' + char(39) +
 ' WHEN ' + char(39) + 'U' + char(39) + 
 ' THEN ' + char(39) + 'USER TABLE' + char(39) +
 ' WHEN ' + char(39) + 'D' + char(39) + 
 ' THEN ' + char(39) + 'DEFAULT CONSTRAINT' + char(39) +
 ' WHEN ' + char(39) + 'F' + char(39) + 
 ' THEN ' + char(39) + 'FOREIGN KEY' + char(39) +
 ' WHEN ' + char(39) + 'IF' + char(39) + 
 ' THEN ' + char(39) + 'INLINE TABLE OR FUNCTION' + char(39) +
 ' WHEN ' + char(39) + 'FN' + char(39) + 
 ' THEN ' + char(39) + 'SCALAR FUNCTION' + char(39) +
 ' WHEN ' + char(39) + 'TF' + char(39) + 
 ' THEN ' + char(39) + 'TABLE FUNCTION' + char(39) +
 ' ELSE CAST( ' + char(39) + 'UNKNOWN TYPE ' + char(39) + ' + obj.XTYPE AS VARCHAR(50)) END)' +
',(CASE WHEN (SELECT count(*) FROM '+@ServerName+'syscolumns where name='+char(39)+@ObjectToFind+char(39) + ') > 0 ' +
'THEN 1 ELSE 0 END)' + 
'FROM ' + @ServerName + 'sysobjects as obj ' + 
'LEFT JOIN ' + @ServerName + 'syscomments as helpText ON obj.ID = helpText.ID ' + 
'LEFT JOIN ' + @ServerName + 'syscolumns as syscol ON syscol.ID = obj.ID ' + 
'WHERE helpText.Text LIKE ' + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) + 
' OR syscol.name LIKE '  + char(39) + '%' + ltrim(rtrim(@ObjectToFind)) + '%' + char(39) + 
' ORDER BY obj.name '
SET @StringToExecute = LTRIM(RTRIM(@StringToExecute))
PRINT LEN(@StringToExecute)
--
exec sp_executesql @StringToExecute
--
IF (@@ERROR != 0) BEGIN
SET @ReturnCode = 1
GOTO END_PROCEDURE
END
--
UPDATE @ServerDatabaseTables
SET Processed = 1
WHERE TempTblID = @DBToProcess
END
--
SELECT * FROM #ServerDatabaseObjectUsage
--
DROP TABLE #ServerDatabaseObjectUsage
--
SET @ReturnCode = 0

END_PROCEDURE:
IF (@ReturnCode != 0) BEGIN
SET @ResultMessage = 'A NON ZERO Return code has occured, Please investigate this problem ' + CAST(@ReturnCode AS VARCHAR(2))
END ELSE BEGIN
SET @ResultMessage = 'OK'
END
RETURN @ReturnCode