接手舊系統時,資料庫缺乏文件,需快速找出所有包含特定字串 (如員工姓名) 的資料表與欄位。手動逐表查詢不切實際,故編寫通用預存程序自動掃描。
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 '王小明' -- 尋找所有字串欄位等於 '王小明' 的資料表與欄位
INFORMATION_SCHEMA.COLUMNS 替代 syscolumns。QUOTENAME 防止 SQL 注入 (已在範例中採用)。NVARCHAR/NCHAR (xtype 231/239) 及 MAX 型別。LIKE 模糊比對參數,增加彈性。sys.dm_exec_describe_first_result_set 等 DMV 方案。原始碼使用舊版系統表,請在 SQL Server 2000 環境參考;新版本請依建議調整。