How to search one specific object among databases

-- Run on the SVANWARSQL02

DECLARE @SearchString VARCHAR(255)

DECLARE @tblName    VARCHAR(300)      -- each table from  select * from StagingData.dbo.stgETL0Cleanup  delete from StagingData.dbo.stgETL0Cleanup where searchstr ='ch_ivr_audit'
                           
DECLARE @db_name1    VARCHAR(300)
DECLARE @db_name2    VARCHAR(300)
DECLARE @sql        VARCHAR(500)
DECLARE @sql1       VARCHAR(500)
DECLARE @counter    INT
DECLARE @error_num  INT

DECLARE @name VARCHAR(50)

SET @counter = 0
--set @name = 'blabla'
--select searchstr,@name from StagingData.dbo.stgETL0Cleanup

--TRUNCATE TABLE StagingData.dbo.ETL0_SearchObjectResults

--IF NOT EXISTS(SELECT * FROM StagingData..sysobjects WHERE ID = OBJECT_ID('StagingData.dbo.ETL0_SearchObjectResults'))
--   BEGIN
--    CREATE TABLE StagingData.dbo.ETL0_SearchObjectResults
--        (   
--        sp_name                        VARCHAR(300),
--        dbName                        VARCHAR(300),
--        tblName                        VARCHAR(300),
--        ID                            VARCHAR(1000)
--        )
--    END
   
--TRUNCATE TABLE StagingData.dbo.ETL0_lkpResults   
   
--IF NOT EXISTS(SELECT * FROM StagingData..sysobjects WHERE ID = OBJECT_ID('StagingData.dbo.ETL0_lkpResults'))
--   BEGIN
--    CREATE TABLE StagingData.dbo.ETL0_lkpResults
--        (   
--        dbName                        VARCHAR(300),
--        tblName                        VARCHAR(300),
--        ID                            VARCHAR(1000)
--        )     
--   END 
                   
--DECLARE tbl_cursor CURSOR FOR
--SELECT searchstr
--FROM StagingData.dbo.stgETL0Cleanup                   

                                                       
DECLARE db_cursor CURSOR FOR 
SELECT name                 -- get the list of all databases on this server
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','ReportServer','ReportServerExternal','ReportServerExternalTempDB','ReportServerTempDB',
'Strata','TelcoReconciliation','TestForReplication','VanBMCProduction_Backup','MapInfo','RepMD','CommonEvent','CommonEvent_2011Q1',
'CommonEvent_2011Q2','CommonEvent_2011Q3','QuestSoftware','QuestWorkDatabase','ProdAdminDB_Backup','EventData_Detail_cw','Chr_Utils_WrkDB',
'VanReplicaOld'
) 
ORDER by name
   
   
    OPEN db_cursor

 
FETCH NEXT FROM db_cursor INTO @name

While @@FETCH_STATUS = 0
BEGIN
    --OPEN tbl_cursor
    --FETCH NEXT FROM tbl_cursor INTO @tblname
   
    --    While @@FETCH_STATUS = 0
    --        BEGIN   
           
                --SET @counter = @counter + 1
                       
                SET @db_name1 =@name+'.dbo.'+'syscomments'
                SET @db_name2 =@name+'.dbo.'+'sysobjects'      
               

                SELECT @sql =                                        
                'SELECT DISTINCT '''+@name+''',o.name,'+CONVERT(varchar(1000),@counter)+' From '+@db_name1+' s JOIN'+' '+@db_name2+' o ON s.id = o.id '
               
                --SELECT @sql = @sql+' WHERE  s.text like '+'''%'+@tblname+'%'''
                SELECT @sql = @sql+' WHERE  s.text like '+'''%'+'cc_optimal_settlement_toberemoved'+'%'''   --->>>>> individual search, change the name between % %
                --SELECT @sql ='INSERT INTO StagingData.dbo.ETL0_SearchObjectResults (sp_name,ID)  '+@sql   
                EXEC(@sql)
               
                Select @error_num = @@ERROR               
                IF @error_num <> 0  Print @sql
               
                --SELECT @sql1 = 'SELECT '''+@name+''','''+@tblName+''','+CONVERT(varchar(1000),@counter)
                --SELECT @sql1 = 'INSERT INTO StagingData.dbo.ETL0_lkpResults(dbName,tblName,ID) '+@sql1
       
                --EXEC(@sql1)
               
                --Select @error_num = @@ERROR
                --IF @error_num <> 0  Print @sql
               
               
                --FETCH NEXT FROM tbl_cursor INTO @tblname
               
        --    END
        --Close tbl_cursor

       
    FETCH NEXT FROM db_cursor INTO @name
END   

--DEALLOCATE tbl_cursor
Close db_cursor
DEALLOCATE db_cursor

--select distinct l.tblName,l.dbName,r.sp_name from StagingData.dbo.ETL0_SearchObjectResults r join StagingData.dbo.ETL0_lkpResults l on r.ID = l.ID order by l.tblName,l.dbName,r.sp_name

 

--SELECT DISTINCT o.name, 'Chr_Utils','ch_ivr_audit'
--From Chr_Utils.dbo.syscomments s JOIN Chr_Utils.dbo.sysobjects o ON s.id = o.id 
--WHERE  s.text like '%ch_ivr_audit%'

--, '+['@name']+','+['@tblname']+