跨資料庫快速尋找特定物件：SQL Server 2005 與 2000/2005 通用腳本

背景

在效能調校時，常需要知道某張表、視圖或預存程序究竟存在於哪個資料庫。SQL Server 2005 引入集合運算（INTERSECT/EXCEPT），讓這項工作變得極簡單；舊版則需透過暫存表輔助。

使用方式

1. 將「使用者修改區域」的 @target 變數改為欲查詢的物件名稱集合（以 UNION ALL 串接）。
2. 複製整段腳本到查詢分析器執行。
3. 結果會列印 Object(s) Found In Database: <資料庫名>。

SQL Server 2005 專用版（集合運算）

USE master;
DECLARE @target varchar(max);
---------------- 使用者修改區域 ---------------
-- 範例
-- SET @target = N'
-- SELECT ''ASPnet_Paths'' UNION ALL
-- SELECT ''ASPnet_Profile''
-- ';
SET @target = N'
SELECT ''ASPnet_Paths'' UNION ALL
SELECT ''ASPnet_Profile''
';
---------------- 使用者修改區域 ---------------
DECLARE @cmd varchar(max);
DECLARE cur_cmd CURSOR FOR
SELECT
    'USE ' + name + ';' +
    'IF NOT EXISTS (' +
        @target +
        ' EXCEPT ' +
        'SELECT name FROM sys.objects ' +
        'INTERSECT (' + @target + ')' +
    ')' +
    'PRINT ''Object(s) Found In Database: ' + name + ''''
FROM sys.databases
WHERE name NOT IN ('master','tempdb','model','msdb');

OPEN cur_cmd;
FETCH NEXT FROM cur_cmd INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@cmd);
    -- PRINT @cmd; -- 僅供除錯
    FETCH NEXT FROM cur_cmd INTO @cmd;
END
PRINT N'Powered by killkill';
CLOSE cur_cmd;
DEALLOCATE cur_cmd;

SQL Server 2000 / 2005 通用版（暫存表）

USE master;
DECLARE @target varchar(8000);
---------------- 使用者修改區域 ---------------
SET @target = N'
SELECT ''CZCOK2'' UNION ALL
SELECT ''CZGSKG''
';
---------------- 使用者修改區域 ---------------
DECLARE @cmd varchar(8000);
DECLARE cur_cmd CURSOR FOR
SELECT
    'USE ' + name + '; ' +
    N'
    SET NOCOUNT ON;
    DECLARE @targetCount int, @foundCount int;
    IF OBJECT_ID(''tempdb..#tmpObjectNames'') IS NOT NULL
        DROP TABLE #tmpObjectNames;
    CREATE TABLE #tmpObjectNames (name varchar(500));
    INSERT INTO #tmpObjectNames ' + @target + '
    SELECT @foundCount = COUNT(*) FROM sysobjects
        INNER JOIN #tmpObjectNames ON sysobjects.name = #tmpObjectNames.name COLLATE Chinese_PRC_CI_AI_WS;
    SELECT @targetCount = COUNT(*) FROM #tmpObjectNames;
    IF (@foundCount = @targetCount)
        PRINT ''Object(s) Found In Database: ' + name + ''';
    SET NOCOUNT OFF;
    DROP TABLE #tmpObjectNames;
    '
FROM sysdatabases
WHERE name NOT IN ('master','tempdb','model','msdb');

OPEN cur_cmd;
FETCH NEXT FROM cur_cmd INTO @cmd;
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC(@cmd);
    -- PRINT @cmd; -- 僅供除錯
    FETCH NEXT FROM cur_cmd INTO @cmd;
END
PRINT N'Powered by killkill';
CLOSE cur_cmd;
DEALLOCATE cur_cmd;

適用場景

當實例內有數十個資料庫，需快速定位某幾個核心表所在庫時，這兩段腳本能大幅減少人工檢查時間。