Search database objects

Zane

SET NOCOUNT ON;

CREATE TABLE ##Zane
(
    db_nm sysname
,   obj_nm	nvarchar(128)
,   object_id	int
,   definition	nvarchar(max)
,   uses_ansi_nulls	bit
,   uses_quoted_identifier	bit
,   is_schema_bound	bit
,   uses_database_collation	bit
,   is_recompiled	bit
,   null_on_null_input	bit
,   execute_as_principal_id	int
);

DECLARE CSR CURSOR
READ_ONLY
FOR SELECT D.name FROM sys.databases AS D

DECLARE
    @dbname sysname
,   @QueryTemplate nvarchar(4000)
,   @Query nvarchar(4000);

SET @QueryTemplate = N'
USE <DBNAME/>
INSERT INTO ##Zane
SELECT
    db_name(db_id()) AS db_nm
,   OBJECT_NAME(m.object_id) AS obj_nm
,   m.*
FROM
    sys.sql_modules m
WHERE
    m.definition LIKE N''%SIRekonAssignments%'';
'

OPEN CSR

FETCH NEXT FROM CSR INTO @dbname
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
        SET @Query = REPLACE(@QueryTemplate, '<DBNAME/>', @dbname)
        EXECUTE sys.sp_executesql @query, N''
	END
	FETCH NEXT FROM CSR INTO @dbname
END

CLOSE CSR
DEALLOCATE CSR
GO


SELECT
    *
FROM
    ##Zane AS Z