Sql Server各系統表說明及使用方法

# SQL Server各系統表說明及使用方法

## 一、系統表概述

SQL Server的系統表（System Tables）是存儲數據庫元數據的核心組件，記錄了數據庫對象、配置信息、權限等關鍵數據。這些表主要存在于`master`數據庫和用戶數據庫的`sys`架構下，從SQL Server 2005開始，系統表逐步被**系統視圖**替代以提高安全性。

## 二、主要系統表/視圖分類

### 1. 數據庫級系統視圖
位于每個數據庫的`sys`架構下：

| 視圖名稱          | 說明                          | 常用查詢示例                     |
|-------------------|-----------------------------|--------------------------------|
| `sys.objects`     | 存儲所有數據庫對象（表、視圖、存儲過程等） | `SELECT * FROM sys.objects WHERE type='U'`（查詢所有用戶表） |
| `sys.tables`      | 專用于用戶表信息                | `SELECT name,create_date FROM sys.tables` |
| `sys.columns`     | 記錄所有表的列定義              | `SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('表名')` |
| `sys.indexes`     | 索引信息                      | `SELECT * FROM sys.indexes WHERE object_id=OBJECT_ID('表名')` |

### 2. 服務器級系統視圖
位于`master`數據庫的`sys`架構：

| 視圖名稱            | 說明                     | 使用場景                     |
|--------------------|------------------------|----------------------------|
| `sys.databases`    | 所有數據庫基本信息         | 查看數據庫狀態、兼容級別等       |
| `sys.server_principals` | 服務器登錄賬戶信息      | 管理登錄權限                 |
| `sys.configurations` | 服務器配置選項          | `sp_configure`結果的詳細視圖 |

### 3. 動態管理視圖(DMV)
提供實時服務器狀態信息：

```sql
-- 查詢當前連接會話
SELECT session_id, login_name, status 
FROM sys.dm_exec_sessions

-- 查看正在執行的SQL
SELECT t.text, s.* 
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t

三、關鍵系統表詳解

1. sys.objects

核心字段： - object_id：對象唯一標識 - name：對象名稱 - type：對象類型（U=表，V=視圖，P=存儲過程等）

典型應用：

-- 查找特定類型的對象
SELECT name, type_desc, create_date
FROM sys.objects
WHERE type IN ('U','V')
ORDER BY create_date DESC

2. sys.columns

重要字段： - object_id：所屬表的ID - name：列名 - system_type_id：數據類型ID - max_length：最大長度

列信息查詢：

-- 獲取表結構詳情
SELECT c.name 列名, t.name 數據類型, c.max_length
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.Employee')

3. sys.indexes

關鍵字段： - index_id：0=堆，1=聚集索引，>1=非聚集索引 - type：索引類型（0=堆，1=聚集，2=非聚集等）

索引分析示例：

-- 查看表的索引情況
SELECT i.name 索引名, i.type_desc, c.name 列名
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID('Orders')

四、實用查詢技巧

1. 查找對象依賴關系

-- 查找存儲過程引用的表
SELECT referencing_id, o.name, o.type_desc
FROM sys.sql_expression_dependencies d
JOIN sys.objects o ON d.referencing_id = o.object_id
WHERE referenced_id = OBJECT_ID('Products')

2. 空間使用分析

-- 查看表空間占用
SELECT 
    t.name 表名,
    s.row_count 行數,
    SUM(a.total_pages) * 8 AS 總空間KB
FROM sys.tables t
JOIN sys.dm_db_partition_stats s ON t.object_id = s.object_id
JOIN sys.allocation_units a ON s.partition_id = a.container_id
GROUP BY t.name, s.row_count

3. 權限查詢

-- 檢查表權限
SELECT 
    u.name 用戶名,
    p.permission_name 權限,
    o.name 對象名
FROM sys.database_permissions p
JOIN sys.objects o ON p.major_id = o.object_id
JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
WHERE o.name = 'Customer'

五、注意事項

1. 直接修改系統表的風險：禁止直接UPDATE/DELETE系統表，應使用專用系統存儲過程如sp_rename等
2. 版本差異：SQL Server 2008與2019的系統表結構可能存在差異
3. 替代方案：優先使用INFORMATION_SCHEMA視圖（標準化程度更高）
4. 性能影響：復雜系統表查詢可能對生產環境造成負載

六、總結

掌握系統表的使用能極大提升DBA工作效率，建議： - 日常管理使用sys視圖而非直接訪問系統表 - 結合DMV進行性能監控 - 定期備份master數據庫（包含關鍵系統信息）

”`

（注：實際字數約1500字，可根據需要調整部分章節的詳細程度）