SQL Server 查询所有数据库中的表、视图与存储过程

了解关键系统对象

SQL Server 中的每个数据库都包含一个名为 sysobjects 的系统表（在较新版本中通常通过 sys.objects 等系统视图访问），它存储了当前数据库中的所有对象信息，包括用户表、视图、触发器、约束、存储过程和日志等。理解其核心字段有助于编写精准查询：

• name：对象名称（如表名、存储过程名）。
• id：对象的唯一标识号。
• xtype：对象类型代码，是筛选不同对象的关键（例如 'U' 代表用户表，'V' 代表视图，'P' 代表存储过程）。
• uid：所有者对象的用户 ID。

准备条件：配置扩展存储过程（可选）

若需获取 SQL Server 实例名称或执行外部命令脚本，可能需要开启 xp_cmdshell 扩展存储过程。请注意，出于安全考虑，默认情况下该功能通常是禁用的，且不建议在生产环境中长期开启。

第1步：开启 xp_cmdshell 并重启配置

目的：允许服务器执行操作系统命令，便于获取实例列表等信息。
入口：SQL Server Management Studio (SSMS) 查询窗口。
动作：执行以下配置语句。

-- 显示高级选项
sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
-- 启用 xp_cmdshell
sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
GO

完成状态：无报错返回，配置已应用。随后可通过 EXEC sys.xp_cmdshell 'sqlcmd -Lc' 获取当前服务器的实例名称列表。

操作顺序：使用 sysobjects 查询数据库元数据

该方法适用于熟悉传统系统表的场景，能够灵活地按库筛选不同类型的对象。

第1步：获取所有数据库名称

目的：列出当前 SQL Server 实例下挂载的所有数据库。
入口：连接至 master 系统数据库或任意数据库上下文。
动作：查询 SysDatabases 系统表。

-- 从 master 库获取所有数据库名并排序
SELECT Name FROM Master..SysDatabases ORDER BY Name;

完成状态：返回当前服务器所有数据库的名称列表。

第2步：获取指定数据库中的用户表

目的：查找某个库中所有的用户创建的数据表。
入口：切换至目标数据库（如 yeekang）。
动作：查询 sysobjects 表，筛选类型为 'U'。

USE yeekang; -- 切换目标数据库
SELECT * FROM sysobjects WHERE xtype = 'U' ORDER BY name;

完成状态：返回该数据库中所有用户表的详细记录。

第3步：获取指定数据库中的视图

目的：列出库中定义的所有视图。
入口：同上。
动作：筛选 xtype 为 'V'。

SELECT * FROM sysobjects WHERE xtype = 'V' ORDER BY name;

第4步：获取指定数据库中的存储过程

目的：列出库中包含的逻辑处理单元。
入口：同上。
动作：筛选 xtype 为 'P'。

SELECT * FROM sysobjects WHERE xtype = 'P' ORDER BY name;

第5步：获取指定数据库中的触发器

目的：列出响应 INSERT、UPDATE 或 DELETE 操作的触发器逻辑。
入口：同上。
动作：筛选 xtype 为 'TR'。

SELECT * FROM sysobjects WHERE xtype = 'TR' ORDER BY name;

进阶操作：获取详细的表结构信息

仅知道表名往往不够，开发人员通常需要查看字段名、数据类型、主键约束及备注。这需要通过连接多个系统表来实现。

第6步：联合查询获取完整表结构与字段详情

目的：生成一份包含表名、表说明、字段序号、字段名、类型、长度、是否主键/标识/可空及默认值的结构化报告。
入口：目标数据库。
动作：关联 syscolumns（字段）、systypes（类型）、sysobjects（对象）、syscomments（默认值定义）及 sys.extended_properties（扩展属性/注释）。
参数：将 OBJECT_ID('Userinfo') 替换为实际需要分析的表名。

SELECT
    表名 = CASE WHEN a.colorder = 1 THEN d.name ELSE '' END,
    表说明 = CASE WHEN a.colorder = 1 THEN ISNULL(f.value, '') ELSE '' END,
    字段序号 = a.colorder,
    字段名 = a.name,
    标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END,
    主键 = CASE WHEN EXISTS (
        SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND name IN (
            SELECT name FROM sysindexes WHERE indid IN (
                SELECT indid FROM sysindexkeys WHERE id = a.id AND colid = a.colid
            ))
        ) THEN '√' ELSE '' END,
    类型 = b.name,
    占用字节数 = a.length,
    长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
    小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0),
    允许空 = CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END,
    默认值 = ISNULL(e.text, ''),
    字段说明 = ISNULL(g.[value], '')
FROM syscolumns a
LEFT JOIN systypes b ON a.xusertype = b.xusertype
INNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.name <> 'dtproperties'
LEFT JOIN syscomments e ON a.cdefault = e.id
LEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_id
LEFT JOIN sys.extended_properties f ON d.id = f.major_id AND f.minor_id = 0
WHERE a.id = OBJECT_ID('Userinfo') -- 替换为实际表名
ORDER BY a.id, a.colorder;

完成状态：返回指定表的详细结构字典，包括主键标记和扩展属性说明。

替代方案：使用标准系统视图

为了遵循 ANSI SQL 标准或提高跨版本兼容性，推荐使用 information_schema 视图。虽然功能稍有限制（如不直接提供扩展属性），但查询语法更简洁统一。

第7步：使用 information_schema 快速获取基础元数据

目的：在不依赖特定厂商扩展的情况下，获取数据库、表的基本信息。
入口：任何用户数据库。
动作：查询标准系统视图。

• 所有数据库列表：通常在 sys.databases 中查看更佳，但 information_schema 主要用于库内对象。
• 所有用户表：

  SELECT * FROM information_schema.tables WHERE table_type = 'BASE TABLE';

• 特定表的所有字段：

  SELECT * FROM information_schema.columns WHERE table_name = 'tablename';

注意：sysobjects 方法在老旧系统或需要深度调试（如获取扩展注释）时仍具优势，而 information_schema 更适合标准化报表生成。

总结与验证

通过上述步骤，您可以从实例层面的数据库列表，深入至单个库内的表、视图、存储过程乃至具体的字段级别结构信息。在使用涉及删除或高危操作（如启用 xp_cmdshell）前，请确保已在测试环境中验证权限和影响。以上就是通过系统数据库获取用户所有数据库中的视图、表、存储过程的详细内容，更多关于 SQL Server 数据库元数据管理的资料请关注本站其它相关文章！