SQL Server 系統表應用實戰：元資料查詢與批量管理

系統表概覽

系統表儲存 SQL Server 所有元資料，通常以 sys 開頭。掌握系統表可實現動態 SQL 建構、物件存在性檢查、批量 DDL 操作等進階管理。

核心系統表

常用 xtype (物件類型) 代碼

實戰應用範例

1. 判斷資料庫/物件是否存在

-- 資料庫
IF EXISTS (SELECT 1 FROM sys.databases WHERE name = 'MyDB')
    PRINT 'DB exists';

-- 使用者表
IF EXISTS (SELECT 1 FROM sys.objects WHERE name = 'MyTable' AND type = 'U')
    PRINT 'Table exists';

2. 查詢所有使用者建立的表

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'U' AND is_ms_shipped = 0
ORDER BY name;

3. 查詢所有預存程序

SELECT name, create_date
FROM sys.objects
WHERE type = 'P' AND is_ms_shipped = 0;

4. 批量產生 DROP TABLE 腳本

SELECT 'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + ';'
FROM sys.objects
WHERE type = 'U' AND is_ms_shipped = 0;

5. 批量重建所有索引

SELECT 'ALTER INDEX ALL ON ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name) + ' REBUILD;'
FROM sys.objects o
WHERE o.type = 'U' AND o.is_ms_shipped = 0;

6. 查詢資料表欄位定義

SELECT c.name, t.name AS type_name, c.max_length, c.precision, c.scale, c.is_nullable, c.is_identity
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.MyTable')
ORDER BY c.column_id;

現代替代：目錄檢視 vs 系統表

建議使用目錄檢視 (sys.objects, sys.columns, sys.indexes...) 而非舊版系統表 (sysobjects, syscolumns...)，原因：

• 目錄檢視為官方支援介面，相容性保證
• 效能更好，支援資料行存儲
• 提供更豐富的中繼資料欄位

權限注意

查詢系統表/目錄檢視需要 VIEW DEFINITION、VIEW DATABASE STATE 或資料庫擁有者權限。