如何通过T-SQL获取SQL Server的完整建表脚本？

当然可以用T-SQL获取包含约束、键和索引的完整建表脚本！你提到的SELECT INTO确实有局限性——它只能复制基础的列结构（数据类型、是否允许为空）和数据（如果不写WHERE 1=2的话），但不会携带主键、外键、索引、默认约束这些关键对象，所以要获取完整脚本得用其他方法。

下面分享几种实用的T-SQL方案：

1. 手动拼接系统视图生成完整脚本 #

通过查询SQL Server的系统目录视图，我们可以拼接出包含所有对象的建表脚本，这里分几个部分来实现：

生成基础表结构（含默认约束） #

这个查询会生成表的CREATE TABLE语句，包含列定义和默认值约束：

SELECT 
    'CREATE TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] (' + CHAR(13) + CHAR(10) +
    STRING_AGG(
        '    [' + c.name + '] ' + 
        UPPER(tp.name) + 
        CASE WHEN tp.name IN ('varchar', 'nvarchar', 'char', 'nchar') THEN 
            '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR) END + ')' 
        ELSE '' END +
        CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE ' NULL' END +
        CASE WHEN dc.definition IS NOT NULL THEN ' DEFAULT ' + dc.definition ELSE '' END,
        ',' + CHAR(13) + CHAR(10)
    ) WITHIN GROUP (ORDER BY c.column_id) + CHAR(13) + CHAR(10) +
    ')' AS CreateTableScript
FROM 
    sys.tables t
JOIN 
    sys.columns c ON t.object_id = c.object_id
JOIN 
    sys.types tp ON c.system_type_id = tp.system_type_id AND c.user_type_id = tp.user_type_id
LEFT JOIN 
    sys.default_constraints dc ON c.default_object_id = dc.object_id
WHERE 
    t.name = 'TableX' AND SCHEMA_NAME(t.schema_id) = 'dbo' -- 替换成你的表名和 schema
GROUP BY 
    t.schema_id, t.name;

生成主键约束脚本 #

这个查询会生成表的主键创建语句：

SELECT 
    'ALTER TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] ADD CONSTRAINT [' + k.name + '] PRIMARY KEY ' +
    CASE WHEN k.type_desc = 'CLUSTERED' THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + ' (' +
    STRING_AGG('[' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END, ', ') +
    ');' AS PrimaryKeyScript
FROM 
    sys.tables t
JOIN 
    sys.key_constraints k ON t.object_id = k.parent_object_id
JOIN 
    sys.index_columns ic ON k.parent_object_id = ic.object_id AND k.unique_index_id = ic.index_id
JOIN 
    sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE 
    t.name = 'TableX' AND SCHEMA_NAME(t.schema_id) = 'dbo' AND k.type = 'PK'
GROUP BY 
    t.schema_id, t.name, k.name, k.type_desc;

生成索引脚本 #

如果表有非主键索引，可以用这个查询生成创建脚本：

SELECT 
    'CREATE ' + CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + 
    i.type_desc + ' INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] (' +
    STRING_AGG('[' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END, ', ') +
    ');' AS IndexScript
FROM 
    sys.tables t
JOIN 
    sys.indexes i ON t.object_id = i.object_id
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 
    t.name = 'TableX' AND SCHEMA_NAME(t.schema_id) = 'dbo' AND i.type_desc != 'HEAP' -- 排除堆表的默认索引
GROUP BY 
    t.schema_id, t.name, i.name, i.type_desc, i.is_unique;

生成外键约束脚本 #

如果表有外键关联，用这个查询生成外键创建语句：

SELECT 
    'ALTER TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY (' +
    STRING_AGG('[' + c.name + ']', ', ') +
    ') REFERENCES [' + SCHEMA_NAME(ref_t.schema_id) + '].[' + ref_t.name + '] (' +
    STRING_AGG('[' + ref_c.name + ']', ', ') +
    ');' AS ForeignKeyScript
FROM 
    sys.tables t
JOIN 
    sys.foreign_keys fk ON t.object_id = fk.parent_object_id
JOIN 
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
JOIN 
    sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
JOIN 
    sys.tables ref_t ON fk.referenced_object_id = ref_t.object_id
JOIN 
    sys.columns ref_c ON fkc.referenced_object_id = ref_c.object_id AND fkc.referenced_column_id = ref_c.column_id
WHERE 
    t.name = 'TableX' AND SCHEMA_NAME(t.schema_id) = 'dbo'
GROUP BY 
    t.schema_id, t.name, fk.name, ref_t.schema_id, ref_t.name;

把这些查询的结果拼接起来，就是完整的建表脚本了。

2. 使用自定义存储过程简化操作 #

如果你经常需要生成脚本，可以把上面的逻辑封装成一个自定义存储过程，比如：

CREATE PROCEDURE GenerateTableScript
    @TableName NVARCHAR(128),
    @SchemaName NVARCHAR(128) = 'dbo'
AS
BEGIN
    SET NOCOUNT ON;

    -- 生成基础表结构
    PRINT '-- 基础表结构';
    SELECT 
        'CREATE TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] (' + CHAR(13) + CHAR(10) +
        STRING_AGG(
            '    [' + c.name + '] ' + 
            UPPER(tp.name) + 
            CASE WHEN tp.name IN ('varchar', 'nvarchar', 'char', 'nchar') THEN 
                '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR) END + ')' 
            ELSE '' END +
            CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE ' NULL' END +
            CASE WHEN dc.definition IS NOT NULL THEN ' DEFAULT ' + dc.definition ELSE '' END,
            ',' + CHAR(13) + CHAR(10)
        ) WITHIN GROUP (ORDER BY c.column_id) + CHAR(13) + CHAR(10) +
        ')'
    FROM 
        sys.tables t
    JOIN 
        sys.columns c ON t.object_id = c.object_id
    JOIN 
        sys.types tp ON c.system_type_id = tp.system_type_id AND c.user_type_id = tp.user_type_id
    LEFT JOIN 
        sys.default_constraints dc ON c.default_object_id = dc.object_id
    WHERE 
        t.name = @TableName AND SCHEMA_NAME(t.schema_id) = @SchemaName
    GROUP BY 
        t.schema_id, t.name;

    PRINT CHAR(13) + CHAR(10) + '-- 主键约束';
    -- 生成主键
    SELECT 
        'ALTER TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] ADD CONSTRAINT [' + k.name + '] PRIMARY KEY ' +
        CASE WHEN k.type_desc = 'CLUSTERED' THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + ' (' +
        STRING_AGG('[' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END, ', ') +
        ');'
    FROM 
        sys.tables t
    JOIN 
        sys.key_constraints k ON t.object_id = k.parent_object_id
    JOIN 
        sys.index_columns ic ON k.parent_object_id = ic.object_id AND k.unique_index_id = ic.index_id
    JOIN 
        sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
    WHERE 
        t.name = @TableName AND SCHEMA_NAME(t.schema_id) = @SchemaName AND k.type = 'PK'
    GROUP BY 
        t.schema_id, t.name, k.name, k.type_desc;

    PRINT CHAR(13) + CHAR(10) + '-- 索引';
    -- 生成索引
    SELECT 
        'CREATE ' + CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END + 
        i.type_desc + ' INDEX [' + i.name + '] ON [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] (' +
        STRING_AGG('[' + c.name + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END, ', ') +
        ');'
    FROM 
        sys.tables t
    JOIN 
        sys.indexes i ON t.object_id = i.object_id
    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 
        t.name = @TableName AND SCHEMA_NAME(t.schema_id) = @SchemaName AND i.type_desc != 'HEAP'
    GROUP BY 
        t.schema_id, t.name, i.name, i.type_desc, i.is_unique;

    PRINT CHAR(13) + CHAR(10) + '-- 外键约束';
    -- 生成外键
    SELECT 
        'ALTER TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] ADD CONSTRAINT [' + fk.name + '] FOREIGN KEY (' +
        STRING_AGG('[' + c.name + ']', ', ') +
        ') REFERENCES [' + SCHEMA_NAME(ref_t.schema_id) + '].[' + ref_t.name + '] (' +
        STRING_AGG('[' + ref_c.name + ']', ', ') +
        ');'
    FROM 
        sys.tables t
    JOIN 
        sys.foreign_keys fk ON t.object_id = fk.parent_object_id
    JOIN 
        sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    JOIN 
        sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
    JOIN 
        sys.tables ref_t ON fk.referenced_object_id = ref_t.object_id
    JOIN 
        sys.columns ref_c ON fkc.referenced_object_id = ref_c.object_id AND fkc.referenced_column_id = ref_c.column_id
    WHERE 
        t.name = @TableName AND SCHEMA_NAME(t.schema_id) = @SchemaName
    GROUP BY 
        t.schema_id, t.name, fk.name, ref_t.schema_id, ref_t.name;
END
GO

使用的时候只需要执行：

EXEC GenerateTableScript @TableName = 'TableX', @SchemaName = 'dbo';

就能直接在查询窗口得到完整的建表脚本了。

补充说明 #

• SELECT INTO的定位是快速复制表结构和数据，它的设计目标就不是完整复制所有对象，所以遇到需要约束、索引的场景，就需要用上面的T-SQL方法。
• 如果你用SQL Server Management Studio（SSMS），也可以右键表→"生成脚本"，可视化选择要包含的对象，但你要的是T-SQL方案，上面的方法就完全满足需求。

内容的提问来源于stack exchange，提问作者user3569267