萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

實用預存程序 (二):搜尋全資料庫字串欄位精確匹配指定值

應用場景

接手舊系統時,資料庫缺乏文件,需快速找出所有包含特定字串 (如員工姓名) 的資料表與欄位。手動逐表查詢不切實際,故編寫通用預存程序自動掃描。

預存程序:searchname

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'searchname' AND type = 'P')
    DROP PROCEDURE searchname
GO

CREATE PROCEDURE searchname @sname VARCHAR(10)
AS
BEGIN
    SET NOCOUNT ON

    -- 暫存結果表
    CREATE TABLE #TableList (
        tablename CHAR(200),
        colname   CHAR(200)
    )

    DECLARE @table VARCHAR(200), @col VARCHAR(200)
    DECLARE @sql NVARCHAR(1000)

    -- 游標遍歷所有使用者資料表
    DECLARE curTab CURSOR FOR 
        SELECT name FROM sysobjects WHERE xtype = 'U'
    OPEN curTab
    FETCH NEXT FROM curTab INTO @table

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- 遍歷該表所有字串型欄位 (xtype 167=VARCHAR, 175=CHAR)
        DECLARE curCol CURSOR FOR
            SELECT name FROM syscolumns 
            WHERE (xtype = 167 OR xtype = 175) 
              AND id = OBJECT_ID(@table)
        OPEN curCol
        FETCH NEXT FROM curCol INTO @col

        WHILE @@FETCH_STATUS = 0
        BEGIN
            -- 動態 SQL 查詢精確匹配
            SET @sql = N'INSERT INTO #TableList SELECT ''' + @table + ''', ''' + @col + ''' FROM ' + QUOTENAME(@table) + ' WHERE ' + QUOTENAME(@col) + ' = @val'
            EXEC sp_executesql @sql, N'@val VARCHAR(10)', @sname

            FETCH NEXT FROM curCol INTO @col
        END
        CLOSE curCol
        DEALLOCATE curCol

        FETCH NEXT FROM curTab INTO @table
    END
    CLOSE curTab
    DEALLOCATE curTab

    -- 輸出結果
    SELECT DISTINCT tablename, colname FROM #TableList
    DROP TABLE #TableList
END
GO

使用範例

EXEC searchname '王小明'  -- 尋找所有字串欄位等於 '王小明' 的資料表與欄位

改進建議 (現代版 SQL Server 2005+)

  • 改用 INFORMATION_SCHEMA.COLUMNS 替代 syscolumns。
  • 使用 QUOTENAME 防止 SQL 注入 (已在範例中採用)。
  • 支援 NVARCHAR/NCHAR (xtype 231/239) 及 MAX 型別。
  • 加入 LIKE 模糊比對參數,增加彈性。
  • 大型資料庫建議改用全文檢索或 sys.dm_exec_describe_first_result_set 等 DMV 方案。

原始碼使用舊版系統表,請在 SQL Server 2000 環境參考;新版本請依建議調整。

copyright © 萬盛學電腦網 all rights reserved