Sql Server各系统表说明及使用方法

发布时间：2021-10-12 14:55:09 作者：柒染
来源：亿速云阅读：150

# 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'

五、注意事项

直接修改系统表的风险：禁止直接UPDATE/DELETE系统表，应使用专用系统存储过程如sp_rename等
版本差异：SQL Server 2008与2019的系统表结构可能存在差异
替代方案：优先使用INFORMATION_SCHEMA视图（标准化程度更高）
性能影响：复杂系统表查询可能对生产环境造成负载

六、总结

掌握系统表的使用能极大提升DBA工作效率，建议： - 日常管理使用sys视图而非直接访问系统表 - 结合DMV进行性能监控 - 定期备份master数据库（包含关键系统信息）

”`

（注：实际字数约1500字，可根据需要调整部分章节的详细程度）