仅拥有SELECT权限时，如何用系统目录视图重建SQL Server表结构

方法1：调用系统存储过程sp_help #

直接执行：

EXEC sp_help '你的表名'

这个系统存储过程会返回多组结果集，包含表的列名、数据类型、长度、空值约束、主键/索引信息等核心结构数据。虽然不会直接输出CREATE TABLE语句，但返回的信息足够直观，手动拼接的效率远高于自己关联多个系统视图。

方法2：用系统视图直接拼接完整CREATE TABLE语句 #

如果需要直接生成可执行的建表语句，可使用以下查询（针对单表），无需自己编写复杂的转换逻辑：

SELECT 'CREATE TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + '(' + STRING_AGG(
    QUOTENAME(c.name) + ' ' + 
    CASE 
        WHEN c.system_type_id = c.user_type_id THEN ty.name 
        ELSE ut.name 
    END + 
    CASE 
        WHEN ty.name IN ('int', 'bigint', 'smallint', 'tinyint', 'bit', 'date', 'datetime', 'datetime2', 'time', 'uniqueidentifier') THEN ''
        WHEN ty.name IN ('decimal', 'numeric') THEN '(' + CAST(c.precision AS VARCHAR) + ',' + CAST(c.scale AS VARCHAR) + ')'
        WHEN ty.name IN ('char', 'varchar', 'nchar', 'nvarchar') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / CASE WHEN ty.name LIKE 'n%' THEN 2 ELSE 1 END AS VARCHAR) END + ')'
        ELSE ''
    END + 
    CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE ' NULL' END,
    ', '
) WITHIN GROUP (ORDER BY c.column_id) + 
CASE WHEN EXISTS (
    SELECT 1 FROM sys.key_constraints kc
    WHERE kc.parent_object_id = t.object_id AND kc.type = 'PK'
) THEN ', PRIMARY KEY (' + STRING_AGG(QUOTENAME(c.name), ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) + ')' ELSE '' END +
')' AS CreateTableScript
FROM sys.tables t
JOIN sys.schemas s ON t.schema_id = s.schema_id
JOIN sys.columns c ON t.object_id = c.object_id
JOIN sys.types ty ON c.system_type_id = ty.system_type_id
LEFT JOIN sys.types ut ON c.user_type_id = ut.user_type_id
LEFT JOIN sys.index_columns ic ON t.object_id = ic.object_id AND c.column_id = ic.column_id
LEFT JOIN sys.key_constraints kc ON ic.object_id = kc.parent_object_id AND ic.index_id = kc.unique_index_id
WHERE t.name = '你的表名' -- 替换为目标表名
GROUP BY t.object_id, s.name, t.name;

执行后会直接输出包含列定义、数据类型（含自定义类型）、空值约束和主键的完整建表语句，复制结果即可使用。

方法3：通过INFORMATION_SCHEMA视图生成（更简洁） #

INFORMATION_SCHEMA的视图结构更友好，适合快速生成基础表结构：

SELECT 'CREATE TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '(' + STRING_AGG(
    QUOTENAME(COLUMN_NAME) + ' ' + DATA_TYPE + 
    CASE 
        WHEN DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') AND CHARACTER_MAXIMUM_LENGTH IS NOT NULL THEN '(' + CASE WHEN CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR) END + ')'
        WHEN DATA_TYPE IN ('decimal', 'numeric') THEN '(' + CAST(NUMERIC_PRECISION AS VARCHAR) + ',' + CAST(NUMERIC_SCALE AS VARCHAR) + ')'
        ELSE ''
    END + 
    CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE ' NULL' END,
    ', '
) WITHIN GROUP (ORDER BY ORDINAL_POSITION) + 
CASE WHEN EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
    WHERE tc.TABLE_SCHEMA = c.TABLE_SCHEMA AND tc.TABLE_NAME = c.TABLE_NAME AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
) THEN ', PRIMARY KEY (' + STRING_AGG(QUOTENAME(kcu.COLUMN_NAME), ', ') WITHIN GROUP (ORDER BY kcu.ORDINAL_POSITION) + ')' ELSE '' END +
')' AS CreateTableScript
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.TABLE_SCHEMA = kcu.TABLE_SCHEMA AND c.TABLE_NAME = kcu.TABLE_NAME AND c.COLUMN_NAME = kcu.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc ON kcu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE c.TABLE_NAME = '你的表名' -- 替换为目标表名
GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME;

这个方法语法更通用，结果易读，适合快速获取基础表结构定义。

注意事项 #

• 若你的SQL Server版本低于2017，STRING_AGG函数不支持，需替换为FOR XML PATH拼接方式，比如将STRING_AGG(...)替换为：

STUFF((SELECT ', ' + QUOTENAME(c.name) + ' ' + -- 此处拼接列定义逻辑
       FROM sys.columns c WHERE c.object_id = t.object_id
       ORDER BY c.column_id
       FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

• 批量生成多表结构时，可去掉WHERE条件，按表分组拼接即可。

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