SQL Server 获取所有表及字段信息的 SQL 语句

获取数据库表及字段信息的 SQL 查询

在进行数据库开发或维护时，有时需要快速获取某个数据库中所有表的详细结构信息。通过执行特定的 SQL 查询语句，我们可以从系统表中提取出这些元数据，以便生成文档或进行代码生成。

以下查询语句旨在获取所有用户表（User Tables）的信息。若需获取视图（Views）的信息，可将查询条件中的类型标识从 'U'（User Table）替换为 'V'（View）。

第1步：准备 SQL Server 2000 版本的查询脚本

目的：兼容较旧的 SQL Server 2000 环境，利用 sysproperties 系统表获取字段描述（Extended Properties）。

操作说明：

1. 入口：打开 SQL Server Management Studio (SSMS) 的查询编辑器。
2. 动作：输入并执行以下 SQL 语句。

SELECT Sysobjects.name AS TABLE_NAME, 
       syscolumns.Id, 
       syscolumns.name AS COLUMN_NAME,
       systypes.name AS DATA_TYPE, 
       syscolumns.length as CHARACTER_MAXIMUM_LENGTH, 
       sysproperties.[value] AS COLUMN_DESCRIPTION,   
       syscomments.text as COLUMN_DEFAULT, 
       syscolumns.isnullable as IS_NULLABLE 
FROM syscolumns 
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype 
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id 
LEFT OUTER JOIN sysproperties ON 
    (sysproperties.smallid = syscolumns.colid AND sysproperties.id = syscolumns.id) 
LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id 
WHERE syscolumns.id IN (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') 
AND (systypes.name <> 'sysname')
ORDER BY syscolumns.colid

关键点：此版本使用了 sysproperties 视图来关联字段的属性描述。

第2步：准备 SQL Server 2005 及以上版本的查询脚本

目的：适用于 SQL Server 2005、2008、2012 及后续版本。从 2005 版本开始，微软对系统表进行了重构，原有的 sysproperties 被移除，改由 sys.extended_properties 来管理扩展属性。

操作说明：

1. 入口：同样在 SSMS 查询编辑器中。
2. 动作：根据实际使用的数据库版本，选择执行下方的 SQL 语句。

SELECT Sysobjects.name AS TABLE_NAME, 
       syscolumns.Id, 
       syscolumns.name AS COLUMN_NAME,
       systypes.name AS DATA_TYPE, 
       syscolumns.length as CHARACTER_MAXIMUM_LENGTH, 
       sys.extended_properties.[value] AS COLUMN_DESCRIPTION,   
       syscomments.text as COLUMN_DEFAULT, 
       syscolumns.isnullable as IS_NULLABLE 
FROM syscolumns 
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype 
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id 
LEFT OUTER JOIN sys.extended_properties ON 
    (sys.extended_properties.minor_id = syscolumns.colid AND sys.extended_properties.major_id = syscolumns.id) 
LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id 
WHERE syscolumns.id IN (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') 
AND (systypes.name <> 'sysname')
ORDER BY syscolumns.colid

关键区别：

• 在 SQL Server 2000 中，使用 sysproperties 表，通过 smallid 和 id 进行连接。
• 在 SQL Server 2005+ 中，使用 sys.extended_properties 视图，通过 minor_id（对应列ID）和 major_id（对应对象ID）进行连接。

第3步：结果解读与应用

完成判断：执行成功后，您将得到一个包含以下字段的表格：

• TABLE_NAME：表名。
• COLUMN_NAME：字段名称。
• DATA_TYPE：数据类型（如 int, nvarchar 等）。
• CHARACTER_MAXIMUM_LENGTH：最大长度。
• COLUMN_DESCRIPTION：字段的描述或注释（如果已设置）。
• COLUMN_DEFAULT：默认值表达式。
• IS_NULLABLE：是否允许空值（通常返回 1 表示允许，0 表示不允许）。

这些结果可以直接用于生成数据库字典、辅助 ORM 框架映射或在程序初始化阶段自动建表/检查结构。

常见问题与注意事项

• 权限要求：执行此查询通常需要数据库具有足够的读取权限，至少需要能够访问系统视图 sysobjects, syscolumns 等。
• 版本兼容性：务必确认您的数据库版本。如果在 2005+ 版本上运行 2000 版本的脚本，可能会因为找不到 sysproperties 而报错。
• 排除系统对象：xtype = 'U' 确保只查询用户创建的表，排除了系统内部表。如果需要查看所有类型的对象，请修改此条件。
• sysname 过滤：(systypes.name <> 'sysname') 用于过滤掉那些基于 sysname 类型的内部或特殊用途列。

以上就是在 SQL Server 中查询所有表及字段详细内容的详细内容，更多关于 SQL Server 数据库管理的资料请关注本站其它相关文章！