SQL Server建表语句的查询方法和技巧
在SQL Server中，有时我们需要查询某个表的建表语句，以了解表的结构、约束、索引等信息。这样可以方便我们对表进行修改、复制或迁移。SQL Server提供了多种方法来查询建表语句，本文将介绍其中几种常用的方法。
方法一：使用系统视图
SQL Server中有一些系统视图，可以用来查看数据库对象的元数据，包括表、列、索引、约束等。其中，sys.tables视图存储了所有用户定义的表的信息，sys.columns视图存储了所有列的信息，sys.indexes视图存储了所有索引的信息，sys.foreign_keys视图存储了所有外键约束的信息，sys.check_constraints视图存储了所有检查约束的信息，sys.default_constraints视图存储了所有默认约束的信息，等等。
我们可以利用这些系统视图，拼接出建表语句。例如，如果我们想要查询名为student的表的建表语句，可以使用以下SQL语句：
1.- 查询表名和注释
2.- 查询列名、数据类型、长度、是否可空、默认值和注释
case when c.user_type_id = 231 then 'nvarchar(' + cast(c.max_length / 2 as varchar(10)) + ')' -- nvarchar类型需要除以2
when c.user_type_id = 165 then 'varbinary(' + cast(c.max_length as varchar(10)) + ')' -- varbinary类型需要特殊处理
else t.name + isnull('(' + cast(c.max_length as varchar(10)) + ')', '') -- 其他类型直接拼接长度
case when c.is_nullable = 0 then ' not null' else '' end + -- 是否可空
isnull(' default ' + d.definition, '') + -- 默认值
isnull(' constraint ' + d.name, '') + -- 默认约束名
isnull(' primary key', '') + -- 主键标识
isnull(' identity(' + cast(c.seed_value as varchar(10)) + ',' + cast(c.increment_value as varchar(10)) + ')', '') + -- 标识列
isnull(',' + char(13) + char(10) + ' constraint ' + fk.name + ' foreign key (' + c.name + ') references ' + o.name + '(' + rc.name + ')', '') + -- 外键约束
isnull(',' + char(13) + char(10) + ' constraint ' + ck.name + ' check (' + ck.definition + ')', '') + -- 检查约束
isnull(' -- ' + ep.value, '') as column_info -- 列注释
1.- 查询索引信息
case when i.is_unique = 1 then 'unique ' else '' end + -- 是否唯一
i.type_desc + ' index ' + i.name + ' (' + -- 索引类型和名称
stuff((select ',' + c.name + case when ic.is_descending_key = 1 then ' desc' else '' end -- 列名和排序方向
for xml path('')), 1, 1, '') + ')' as index_info -- 拼接列名
where i.object_id = object_id('student') and i.type <> 0 -- 排除堆表
1.- 查询表结束符
这个SQL语句的结果如下：
create table student ( -- 学生表
gender bit not null default 0 constraint DF_student_gender, -- 性别，0为男，1为女
age tinyint not null check (age between 18 and 25), -- 年龄，必须在18到25之间
class_id int not null, -- 班级编号
constraint FK_student_class foreign key (class_id) references class(id) -- 外键约束，参考班级表的id列
clustered index PK__student__3213E83F7F60ED59 (id), -- 聚集索引，主键自动创建
nonclustered index IX_student_name (name), -- 非聚集索引，按姓名排序
nonclustered index IX_student_score (score desc) -- 非聚集索引，按成绩降序排序
这个结果就是student表的建表语句，包含了表名、列名、数据类型、长度、是否可空、默认值、主键、标识列、外键约束、检查约束、索引等信息。我们可以将这个结果复制到SQL Server Management Studio中，执行后就可以创建一个与原表结构相同的表。
方法二：使用存储过程
SQL Server中也提供了一些系统存储过程，可以用来生成数据库对象的脚本，包括表、视图、函数、存储过程等。其中，sp_helptext存储过程可以用来显示用户定义的对象的定义语句，sp_help存储过程可以用来显示数据库对象的详细信息，sp_helpindex存储过程可以用来显示表的索引信息。
我们可以利用这些存储过程，生成建表语句。