Hunt For SQL Reference - All Databases

Hunt For SQL Reference - All Databases.sql

-------------------------------------------------------------------------------
-- All DB Catalogues
-- Including Error handling (for when there are permission issues)
-------------------------------------------------------------------------------
DECLARE @search_text  VARCHAR(200);
DECLARE @select_text  NVARCHAR(1000);
DECLARE @command_text VARCHAR(2000);

SET @search_text = 'OBJECT_NAME';

IF OBJECT_ID('tempdb..#DataInformation') IS NOT NULL
    DROP TABLE #DataInformation;

CREATE TABLE #DataInformation
(
    [db_catalog_name] NVARCHAR(MAX),
    [Object_Name]     NVARCHAR(MAX),
    [type_desc]       NVARCHAR(MAX)
);

IF OBJECT_ID('tempdb..#ErrorLogTbl') IS NOT NULL
    DROP TABLE #ErrorLogTbl;

CREATE TABLE #ErrorLogTbl
(
    [ErrorNumber]  BIGINT,
    [ErrorMessage] NVARCHAR(MAX)
);

DECLARE @db_id    AS BIGINT;
DECLARE @db_name  AS VARCHAR(MAX);
DECLARE @DbCursor AS CURSOR;

SET @DbCursor = CURSOR
FOR
SELECT [name]        AS [db_name],
       [database_id] AS [db_id]  
  FROM sys.databases
;

OPEN @DbCursor
FETCH NEXT FROM @DbCursor INTO @db_name, @db_id;
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @select_text = '
    INSERT
      INTO #DataInformation
          ([db_catalog_name], [Object_Name], [type_desc])
    SELECT DISTINCT
           ''' + @db_name + '''  AS db_catalog_name,
           o.name AS Object_Name,
           o.type_desc
      FROM ' + '[' + @db_name + ']' + '.sys.objects o
           LEFT JOIN ' + '[' + @db_name + ']' + '.sys.sql_modules m ON m.object_id = o.object_id
     WHERE LOWER(o.name)       LIKE LOWER(''%' + REPLACE(@search_text, '''', '''''') + '%'')
        OR LOWER(m.definition) LIKE LOWER(''%' + REPLACE(@search_text, '''', '''''') + '%'')
    '
    ;

    BEGIN TRY
        EXEC sp_executesql @select_text;
    END TRY
    BEGIN CATCH
        INSERT
          INTO #ErrorLogTbl
              ([ErrorNumber],[ErrorMessage])
        SELECT ERROR_NUMBER()  AS [ErrorNumber],
               ERROR_MESSAGE() AS [ErrorMessage]
        ;
        PRINT @select_text;
    END CATCH

    FETCH NEXT FROM @DbCursor INTO @db_name, @db_id;
END
 
CLOSE @DbCursor;
DEALLOCATE @DbCursor;

SELECT *
  FROM #DataInformation
;

SELECT *
  FROM #ErrorLogTbl
;

GO