Search stored procedures in all databases which depend on given object

---search below object used in stored procs in all the database

DECLARE @Object_look varchar(256)='gtp_snp'

SET NOCOUNT ON

Print '******Object we are looking "'+@Object_look+'"'

DECLARE @dbNAme varchar(256)

IF OBJECT_ID('tempdb..#t','U') is not null

drop table #t

create table #t (Spname varchar(1024),[db_name] varchar(2000), [text] text)

DECLARE C CURSOR FOR SELECT NAME FROM SYS.SYSDATABASES

OPEN C

FETCH NEXT FROM C INTO @dbNAme

WHILE @@FETCH_STATUS=0

BEGIN

 

  DECLARE @SQLString NVARCHAR(500)

  DECLARE @ParmDefinition NVARCHAR(500)

  DECLARE @rows int

 

  select @SQLString='select o.name,''' + @dbname + ''' as db_name, sc.text from

  ' + @dbname + '.sys.syscomments sc join ' + @dbname + '..sysobjects o on sc.id=o.id

  and o.type=''P''

  and o.category=0 where text like ''%' + @Object_look + '%'''

  ---print @SQLString

  Truncate table #t

  ---exec (@SQLString)

  insert into #t exec (@SQLString)

  select @rows=@@ROWCOUNT

  IF @rows>=1

   Begin 

     Print 'Searching in databaseName= "'+@dbNAme+'"'

     DECLARE @Spanme varchar(1024)

     DECLARE C_in CURSOR FOR SELECT distinct spname FROM #t

     OPEN C_in

     FETCH NEXT FROM C_in INTO @Spanme

     WHILE @@FETCH_STATUS=0

     BEGIN

       print 'Stored proc NAme='+@Spanme

    

     FETCH NEXT FROM C_in INTO @Spanme

     END

     CLOSE C_in

     DEALLOCATE C_in  

   END

FETCH NEXT FROM C INTO @dbNAme

END

CLOSE C

DEALLOCATE C