Hunt For SQL Reference - Single Database

Hunt For SQL Reference - Single Database.sql

-------------------------------------------------------------------------------
-- Current DB Catalogue
-------------------------------------------------------------------------------

DECLARE @search_text  VARCHAR(200);
DECLARE @command_text NVARCHAR(2000);

SET @search_text = 'OBJECT_NAME';

SET @command_text = '
SELECT DISTINCT
       db_name()      AS [db_catalog_name],
       o.[name]       AS [Object_Name],
       m.[definition],
       o.[type_desc],
       s.[name]       AS [schema_name],
       ''['' + s.[name] + ''].['' + o.[name] + '']'' AS [formatted_Name]
  FROM sys.objects o
     LEFT JOIN sys.sql_modules m ON m.[object_id] = o.[object_id]
     LEFT JOIN sys.schemas     s ON o.[schema_id] = s.[schema_id]
 WHERE(   LOWER(m.definition) LIKE LOWER(''%' + REPLACE(@search_text, '''', '''''') + '%'')
       OR LOWER(o.name)       LIKE LOWER(''%' + REPLACE(@search_text, '''', '''''') + '%'')
      )
	  '
;

PRINT @command_text;

EXEC sp_executesql @command_text;

GO