动态复制SQL表时如何保留Numeric数据类型？

我尝试动态复制一张表并保留源表的数据类型，当前查询大部分功能正常，但Numeric类型的列被转换为nvarchar类型，请问需要修改哪些部分才能保留Numeric数据类型？

当前使用的SQL代码 #

DECLARE @TableName = 'Table'
DECLARE @SchemaName = 'Schema'

DECLARE @SQL NVARCHAR(MAX) = 'CREATE TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TablePrefix_' + SUBSTRING(@TableName, 3, LEN(@TableName)-2)) + ' (
    [AuditId] INT IDENTITY(1,1) NOT NULL,
    [AuditAction] VARCHAR(50) NOT NULL,
    [AuditDateTime] DATETIME NOT NULL,    
    ' + 
    STUFF((
        SELECT ',' + '[' + c.name + '] ' + 
                CASE 
                    WHEN c.system_type_id IN (167, 175, 231, 239) AND c.max_length = -1 THEN 'VARCHAR(MAX)'
                    WHEN c.system_type_id IN (167, 175, 231, 239) THEN 'VARCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (106, 108, 165, 173, 231, 239) AND c.max_length = -1 THEN 'NVARCHAR(MAX)'
                    WHEN c.system_type_id IN (106, 108, 165, 173, 231, 239) THEN 'NVARCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length / 2 END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (40) THEN 'CHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (41) THEN 'NCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (48, 52, 56) THEN 'INT'
                    WHEN c.system_type_id IN (127) THEN 'BIGINT'
                    WHEN c.system_type_id IN (59, 60, 62) THEN 'SMALLINT'
                    WHEN c.system_type_id = 104 THEN 'BIT' -- Changed from 'TINYINT' to 'BIT'
                    WHEN c.system_type_id IN (106, 108, 122, 127, 130, 131, 143, 167, 173, 175, 189, 231, 239) THEN TYPE_NAME(c.user_type_id)
                    ELSE TYPE_NAME(c.system_type_id)
                END +
                CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
        FROM sys.columns c
        WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
        FOR XML PATH(''), TYPE
    ).value('.[1]','nvarchar(max)'), 1, 1, '')
    + ')'
EXEC(@SQL)

修改方案 #

问题出在CASE语句的匹配顺序：Numeric（system_type_id=106）和Decimal（system_type_id=108）被错误归类到了NVARCHAR的分支中，导致类型被转换。需要调整CASE分支的顺序和内容：

1. 移除NVARCHAR分支中的106、108：把原代码中两个NVARCHAR判断里的106, 108去掉，避免Numeric类型被错误匹配为NVARCHAR。
2. 优先匹配Numeric/Decimal类型：在CASE开头添加专门的分支处理106和108，直接使用TYPE_NAME(c.user_type_id)保留原类型的精度和小数位数。

修改后的完整代码 #

DECLARE @TableName = 'Table'
DECLARE @SchemaName = 'Schema'

DECLARE @SQL NVARCHAR(MAX) = 'CREATE TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TablePrefix_' + SUBSTRING(@TableName, 3, LEN(@TableName)-2)) + ' (
    [AuditId] INT IDENTITY(1,1) NOT NULL,
    [AuditAction] VARCHAR(50) NOT NULL,
    [AuditDateTime] DATETIME NOT NULL,    
    ' + 
    STUFF((
        SELECT ',' + '[' + c.name + '] ' + 
                CASE 
                    -- 优先处理Numeric/Decimal类型，保留精度和小数位数
                    WHEN c.system_type_id IN (106, 108) THEN TYPE_NAME(c.user_type_id)
                    WHEN c.system_type_id IN (167, 175, 231, 239) AND c.max_length = -1 THEN 'VARCHAR(MAX)'
                    WHEN c.system_type_id IN (167, 175, 231, 239) THEN 'VARCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    -- 移除NVARCHAR分支中的106、108
                    WHEN c.system_type_id IN (165, 173, 231, 239) AND c.max_length = -1 THEN 'NVARCHAR(MAX)'
                    WHEN c.system_type_id IN (165, 173, 231, 239) THEN 'NVARCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length / 2 END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (40) THEN 'CHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (41) THEN 'NCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
                    WHEN c.system_type_id IN (48, 52, 56) THEN 'INT'
                    WHEN c.system_type_id IN (127) THEN 'BIGINT'
                    WHEN c.system_type_id IN (59, 60, 62) THEN 'SMALLINT'
                    WHEN c.system_type_id = 104 THEN 'BIT' -- Changed from 'TINYINT' to 'BIT'
                    WHEN c.system_type_id IN (122, 127, 130, 131, 143, 167, 173, 175, 189, 231, 239) THEN TYPE_NAME(c.user_type_id)
                    ELSE TYPE_NAME(c.system_type_id)
                END +
                CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
        FROM sys.columns c
        WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
        FOR XML PATH(''), TYPE
    ).value('.[1]','nvarchar(max)'), 1, 1, '')
    + ')'
EXEC(@SQL)

关键修改说明 #

• 新增的WHEN c.system_type_id IN (106, 108) THEN TYPE_NAME(c.user_type_id)分支会优先匹配Numeric/Decimal类型，TYPE_NAME(c.user_type_id)会返回带精度的完整类型名（比如numeric(18,2)），不会丢失原类型的属性。
• 原NVARCHAR分支中的106, 108被移除，避免了错误匹配。
• CASE分支的顺序很重要，优先处理特殊类型（如Numeric）可以避免被后续的通用分支错误覆盖。

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