look for given object in all databases (Is it function or stored proc or table or view etc...)

---look for below object in whole server

DECLARE @Object_look varchar(256)='spGetGMhpa'

DECLARE @dbNAme varchar(256)

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 @CNT int

SELECT @SQLString='SELECT @cntOUT=COUNT(*) from '+@dbNAme+'.SYS.OBJECTS WHERE NAME='''+@Object_look+''''

SET @ParmDefinition = N'@cntOUT int OUTPUT'

EXECUTE sp_executesql @SQLString, @ParmDefinition,@cntOUT=@cnt OUTPUT

--SELECT @cnt

IF @CNT>1

BEGIN

RAiserror('This object created created in %s but two places, either in table or storedproc or function',16,-1,@dbNAme) with nowait

END

IF @CNT=1

BEGIN

DECLARE @name varchar(256), @type varchar(10)

SELECT @SQLString='SELECT @nameOUT=name, @typeout=type from '+@dbNAme+'.SYS.OBJECTS WHERE NAME='''+@Object_look+''''

SET @ParmDefinition = N'@nameOUT varchar(256) OUTPUT, @typeout varchar(10) OUTPUT'

EXECUTE sp_executesql @SQLString, @ParmDefinition,@nameOUT=@name OUTPUT,@typeout=@type OUTPUT

DECLARE @TypeNAME varchar(256)

SELECT @TypeNAME=CASE WHEN @type='AF' then 'Aggregate function (CLR)'

WHEN @type='C' then 'CHECK constraint'

WHEN @type='D' then 'DEFAULT (constraint or stand-alone)'

WHEN @type='F' then 'FOREIGN KEY constraint'

WHEN @type='FN' then 'SQL scalar function'

WHEN @type='FS' then 'Assembly (CLR) scalar-function'

WHEN @type='FT' then 'Assembly (CLR) table-valued function'

WHEN @type='IF' then 'SQL inline table-valued function'

WHEN @type='IT' then 'Internal table'

WHEN @type='P' then 'SQL Stored Procedure'

WHEN @type='PC' then 'Assembly (CLR) stored-procedure'

WHEN @type='PG' then 'Plan guide'

WHEN @type='PK' then 'PRIMARY KEY constraint'

WHEN @type='R' then 'Rule (old-style, stand-alone)'

WHEN @type='RF' then 'Replication-filter-procedure'

WHEN @type='S' then 'System base table'

WHEN @type='SN' then 'Synonym'

WHEN @type='SQ' then 'Service queue'

WHEN @type='TA' then 'Assembly (CLR) DML trigger'

WHEN @type='TF' then 'SQL table-valued-function'

WHEN @type='TR' then 'SQL DML trigger'

WHEN @type='TT' then 'Table type'

WHEN @type='U' then 'Table (user-defined)'

WHEN @type='UQ' then 'UNIQUE constraint'

WHEN @type='V' then 'View'

WHEN @type='X' then 'Extended stored procedure' END

Print 'Object we are looking ='+@Object_look

Print 'databaseName='+@dbNAme

Print 'ObjectType='+@TypeNAME

END

FETCH NEXT FROM C INTO @dbNAme

END

CLOSE C

DEALLOCATE C