SQL Server 查詢所有資料表儲存程序：支援模糊查詢與分頁顯示

需求背景

專案資料庫表數量龐大，SSMS 結果視窗預設最多顯示 100 筆資料，無法一次瀏覽所有表結構。需要一個支援分頁、模糊查詢表名、自訂每頁筆數的儲存程序。

儲存程式碼完整版

IF EXISTS (SELECT * FROM sysobjects WHERE [name] = 'pro_selectTableAll')
    DROP PROCEDURE pro_selectTableAll;
GO

CREATE PROCEDURE pro_selectTableAll
    @showCount INT = 100,      -- 每頁顯示筆數，預設 100
    @name_like VARCHAR(50) = '', -- 表名模糊查詢條件（前綴），預設空 = 全部
    @pageIndex INT = 1         -- 頁碼（從 1 開始）
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @tableCount INT;
    DECLARE @index INT = 1;
    
    -- 取得符合條件的總表數
    SELECT @tableCount = COUNT(*) 
    FROM sysobjects 
    WHERE [name] LIKE @name_like + '%';
    
    -- 清理舊暫存表
    IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID('tempdb..#table1'))
        DROP TABLE #table1;
    
    -- 頁碼轉換：使用者輸入 1-based，內部 0-based
    IF (@pageIndex <> 0)
        SET @pageIndex = @pageIndex - 1;
    
    -- 將符合條件的表名分頁查詢存入暫存表（含流水號 id）
    SELECT IDENTITY(INT, 1, 1) AS id, [name] 
    INTO #table1 
    FROM (
        SELECT TOP (@showCount) [name] 
        FROM sysobjects 
        WHERE [name] LIKE @name_like + '%' 
          AND id NOT IN (
              SELECT TOP (@showCount * @pageIndex) [id] 
              FROM sysobjects 
              WHERE [name] LIKE @name_like + '%' 
              ORDER BY [name]
          ) 
        ORDER BY [name]
    ) AS abc;
    
    -- 迴圈輸出每張表的資料
    WHILE @index <= @tableCount
    BEGIN
        IF @index <= @showCount
        BEGIN
            DECLARE @tableName VARCHAR(200);
            SELECT @tableName = [name] FROM #table1 WHERE id = @index;
            
            PRINT '第 ' + CAST(@index AS VARCHAR(20)) + ' 張表，表名：' + @tableName;
            EXEC('SELECT * FROM ' + @tableName);
            
            SET @index = @index + 1;
        END
        ELSE
        BEGIN
            PRINT '結束迴圈';
            BREAK;
        END
    END
    
    -- 顯示本頁查詢到的表清單供對照
    SELECT * FROM #table1;
END
GO

參數說明與執行範例

執行範例

-- 查詢第 2 頁，每頁 10 筆，表名以 tb_ 開頭
EXEC pro_selectTableAll 10, 'tb_', 2;

-- 對照驗證
SELECT TOP 100 * FROM sysobjects WHERE [name] LIKE 'tb_%' ORDER BY [name];

技術要點解析

1. 分頁邏輯：使用 NOT IN (SELECT TOP N ...) 實現類似 OFFSET 的效果（SQL Server 2005/2008 尚無 OFFSET/FETCH）
2. IDENTITY 暫存表：產生流水號方便迴圈逐筆處理
3. 動態 SQL：EXEC('SELECT * FROM ' + @tableName) 實現表名參數化
4. PRINT 進度顯示：在「訊息」頁籤查看處理進度

改進建議

• SQL Server 2012+ 可改用 OFFSET @pageIndex ROWS FETCH NEXT @showCount ROWS ONLY 簡化分頁
• 加入結構描述篩選（schema_name(schema_id)）
• 改為回傳結果集而非 PRINT，方便應用程式串接
• 加入資料列數統計、索引資訊等擴充欄位

結語

這個儲存程序雖然簡單，但解決了 SSMS 100 筆顯示限制的實際痛點。分頁+模糊查詢的組合，讓大型資料庫的表結構瀏覽變得輕鬆可控。