前言

幾百張表的資料庫，欄位名稱記不住放在哪張表——一張張展開 SSMS 物件總管根本沒有效率。這篇整理幾種用系統視圖直接查的方式，從基礎模糊搜尋到跨資料庫都有，下次忘了直接拿來用。

基本搜尋方法

最直接的做法是透過 sys.tables 和 sys.columns 這兩個系統視圖 JOIN 起來查：

1
2
3
4
5
6
7
8
9
10
11
SELECT 
    t.name AS TableName,
    c.name AS ColumnName
FROM 
    sys.tables t
INNER JOIN 
    sys.columns c ON t.object_id = c.object_id
WHERE 
    c.name LIKE '%YourColumnName%'
ORDER BY 
    TableName, ColumnName;

使用說明

  1. 將 YourColumnName 替換成你要搜尋的欄位名稱
  2. % 符號代表萬用字元，可以做模糊搜尋
  3. 查詢結果會顯示所有符合的資料表名稱及欄位名稱

進階搜尋技巧

只知道欄位名稱還不夠，通常還要確認型別、長度、能不能為空。加 JOIN sys.types 一次撈齊：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT 
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    typ.name AS DataType,
    c.max_length AS MaxLength,
    c.precision AS Precision,
    c.scale AS Scale,
    c.is_nullable AS IsNullable
FROM 
    sys.tables t
INNER JOIN 
    sys.columns c ON t.object_id = c.object_id
INNER JOIN 
    sys.types typ ON c.user_type_id = typ.user_type_id
WHERE 
    c.name LIKE '%YourColumnName%'
ORDER BY 
    SchemaName, TableName, ColumnName;

這個查詢會額外顯示：

  • 綱要名稱 (Schema Name)
  • 資料型別 (Data Type)
  • 欄位長度 (Max Length)
  • 精確度 (Precision)
  • 小數位數 (Scale)
  • 是否允許空值 (Is Nullable)

跨資料庫搜尋

欄位散落在多個資料庫時，用 CURSOR 遍歷所有上線的使用者資料庫，結果統一放暫存表：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE TABLE #Results (
    DatabaseName NVARCHAR(128),
    SchemaName NVARCHAR(128),
    TableName NVARCHAR(128),
    ColumnName NVARCHAR(128)
)

DECLARE @SQL NVARCHAR(MAX)
DECLARE @DatabaseName NVARCHAR(128)

DECLARE db_cursor CURSOR FOR 
SELECT name 
FROM sys.databases 
WHERE database_id > 4  -- 排除系統資料庫
AND state_desc = 'ONLINE'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @SQL = '
    USE [' + @DatabaseName + ']
    INSERT INTO #Results
    SELECT 
        ''' + @DatabaseName + ''' AS DatabaseName,
        SCHEMA_NAME(t.schema_id) AS SchemaName,
        t.name AS TableName,
        c.name AS ColumnName
    FROM 
        sys.tables t
    INNER JOIN 
        sys.columns c ON t.object_id = c.object_id
    WHERE 
        c.name LIKE ''%YourColumnName%'''

    EXEC sp_executesql @SQL

    FETCH NEXT FROM db_cursor INTO @DatabaseName
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * FROM #Results
ORDER BY DatabaseName, SchemaName, TableName, ColumnName

DROP TABLE #Results

注意事項

  1. 這個查詢會搜尋所有非系統資料庫
  2. 需要適當的資料庫權限
  3. 執行時間可能較長，視資料庫數量而定

實用小技巧

  1. 精確搜尋：如果知道完整欄位名稱，移除 LIKE 中的 % 符號

    1
    WHERE c.name = 'ExactColumnName'

  2. 區分大小寫：加入 COLLATE Latin1_General_CS_AS

    1
    WHERE c.name LIKE '%ColumnName%' COLLATE Latin1_General_CS_AS

  3. **搜尋檢視表(View)**：修改 sys.tables 為 sys.views

    1
    FROM sys.views t

參考資料