SQL Server动态复制表结构时float自动转real问题如何解决

开发者阿泽

2026-9-1

问题背景

数据库历史备份表规则：业务主表（如MYTABLE1）按年份生成备份副本，命名格式为[原表名]_BCK_[年份]，例如MYTABLE1_BCK_2014、MYTABLE1_BCK_2022，所有业务表均遵循该规则。
年度备份规则：新一年备份表以上一年度最新备份表为模板复制结构，避免主表结构变更未同步到历史备份表的问题。
业务表字段不固定，包含float、nvarchar、varchar、datetime等多种数据类型。
已编写存储过程实现动态识别待备份表、创建结构副本、添加主键/默认约束、同步关联视图的功能，存储过程代码如下：

CREATE OR ALTER PROCEDURE CreateBackupTables(
    @ReferenceYear [int] -- Year on which to create the new backup tables
)
AS
BEGIN
    DECLARE @SqlString [nvarchar] (max) = ''
    DECLARE @SqlStringInsert [nvarchar] (max) = ''
    DECLARE @SqlCreateTable [nvarchar] (max) = ''
    DECLARE @SqlCreateView [nvarchar] (max) = ''
    DECLARE @SqlCreateConstraint [nvarchar] (max) = ''

    /* This table will contain the names of the tables from which to start to create the backup ones, with relative constraints for primary key */
    CREATE TABLE #TableNamesToCreate(
        TableName varchar(50),
        ConstraintName varchar(50),
        ConstraintFields varchar(40)
    )

    /* Read from the system tables the names of the tables on which to create the new backups, starting from the existing ones for the reference year -1 */
    SET @SqlString = N'SELECT TableName, constraint_name, details 
    FROM
    (SELECT DISTINCT SUBSTRING(t.name,1,CHARINDEX(''_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1) +''',t.name)-1) AS [TableName] FROM sys.tables t WHERE t.name LIKE ''%_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ''') A 
    LEFT JOIN
    (SELECT t.[name] AS table_view, isnull(c.[name], i.[name]) AS constraint_name,
        substring(column_names, 1, LEN(column_names)-1) AS [details]
    FROM sys.objects t
        LEFT OUTER JOIN sys.indexes i
            ON t.object_id = i.object_id
        LEFT OUTER JOIN sys.key_constraints c
            ON i.object_id = c.parent_object_id 
            AND i.index_id = c.unique_index_id
        CROSS APPLY (SELECT col.[name] + '', ''
                        FROM sys.index_columns ic
                            INNER JOIN sys.columns col
                                ON ic.object_id = col.object_id
                                AND ic.column_id = col.column_id
                        WHERE ic.object_id = t.object_id
                            AND ic.index_id = i.index_id
                                ORDER BY col.column_id
                                FOR XML PATH ('''') ) D (column_names)
    WHERE is_unique = 1 
    AND t.name LIKE ''%_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + '''
    AND t.is_ms_shipped <> 1) B ON A.TableName = SUBSTRING(B.table_view,1,CHARINDEX(''_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1) +''',B.table_view)-1) 
    ORDER BY TableName, constraint_name'

    /* Generate the data entry string on the tables to be created */
    SET @SqlStringInsert = N'INSERT INTO #TableNamesToCreate ' + @SqlString

    PRINT @SqlStringInsert

    EXECUTE sp_executesql @SqlStringInsert

    /* Verification of the tables found */
    SELECT * FROM #TableNamesToCreate

    /* This table will contain the details of the fields to add to each table (data type, possible length, etc.) */
    CREATE TABLE #Tables(
        TableName varchar(50),
        ColumnId int,
        ColumnName varchar(50),
        ColumnType varchar(10),
        ColumnLength int,
        ColumnPrecision int, 
        ColumnNullable varchar(10)
    )

    /* Read the details of the columns. The fields of type nvarchar and nchar have double length, so it is necessary to divide them by 2 */
    SET @SqlString = N' SELECT t.name, c.column_id, c.name, ty.name, CASE WHEN ty.name in(''nvarchar'',''nchar'') THEN c.max_length/2 ELSE c.max_length END, c.precision, c.is_nullable 
                        FROM sys.all_columns c INNER JOIN 
                        sys.tables t ON c.object_id = t.object_id INNER JOIN 
                        sys.types ty ON c.user_type_id = ty.user_type_id
                        WHERE t.name IN (SELECT DISTINCT SUBSTRING(t.name,1,CHARINDEX(''_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1) +''',t.name)-1) FROM sys.tables t WHERE t.name LIKE ''%_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ''')
                        ORDER BY t.name, c.column_id'
    
    /* Enter the results in the data table */
    SET @SqlStringInsert = N'INSERT INTO #Tables ' + @SqlString
    PRINT @SqlStringInsert
    EXECUTE sp_executesql @SqlStringInsert

    /* Start preparing the table creation scripts */
    DECLARE cursoreNomeTabella CURSOR FOR SELECT TableName, ConstraintFields FROM #TableNamesToCreate
    DECLARE @NomeTabellaDaCreare varchar(50)
    DECLARE @ConstraintFields varchar(40)
    OPEN cursoreNomeTabella
    FETCH NEXT FROM cursoreNomeTabella INTO @NomeTabellaDaCreare, @ConstraintFields
    WHILE @@FETCH_STATUS=0
    BEGIN
        /* I check that the table to be created is not already present on the DB */
        SET @SqlCreateTable = N'IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @NomeTabellaDaCreare + '_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear) + ']'') AND type in (N''U'')) CREATE TABLE [dbo].[' + @NomeTabellaDaCreare + '_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear) + '] ('
    
        DECLARE @SqlCreateField [nvarchar](max) = ''
        DECLARE @SqlConstraint [nvarchar](max) = ''
        DECLARE @ColumnId int,  @ColumnName varchar(50), @ColumnType varchar(10), @ColumnLength int, @ColumnPrecision int, @ColumnNullable varchar(10)
    
        DECLARE cursoreCampi CURSOR FOR SELECT ColumnName, ColumnType, ColumnLength, ColumnPrecision, ColumnNullable FROM #Tables WHERE TableName = @NomeTabellaDaCreare

        OPEN cursoreCampi
        FETCH NEXT FROM cursoreCampi INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnPrecision, @ColumnNullable
        WHILE @@FETCH_STATUS=0
        BEGIN
            DECLARE @ColumnLengthToChar [varchar](4) = 'max'
            IF @ColumnLength != -1 AND @ColumnType NOT IN ('nvarchar','nchar') SET @ColumnLengthToChar = CONVERT(VARCHAR(4),@ColumnLength)
        
            IF @ColumnLength = 0 AND @ColumnType IN('nvarchar','nchar') SET @ColumnLengthToChar = 'max'
            IF @ColumnLength > 0 AND @ColumnType IN('nvarchar','nchar') SET @ColumnLengthToChar = CONVERT(VARCHAR(4),@ColumnLength)

            SET @SqlCreateField = @SqlCreateField + @ColumnName + ' [' + @ColumnType + ']'  
            SET @SqlCreateField = @SqlCreateField + CASE WHEN @ColumnType NOT IN ('int','bit','bigint','date','datetime','datetime2','ntext') THEN '(' + @ColumnLengthToChar + ') ' ELSE ' ' END
            SET @SqlCreateField = @SqlCreateField + CASE WHEN @ColumnNullable = 0 THEN 'NOT NULL' ELSE 'NULL' END + ', '
            
            FETCH NEXT FROM cursoreCampi INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnPrecision, @ColumnNullable
        END

        SET @SqlCreateTable = @SqlCreateTable + @SqlCreateField 
        IF @ConstraintFields IS NOT NULL
            BEGIN
                SET @SqlConstraint = ' CONSTRAINT [PK_' + @NomeTabellaDaCreare + '_BCK_' + CONVERT(VARCHAR(4), @ReferenceYear) + '] PRIMARY KEY CLUSTERED (' + @ConstraintFields + 
                                                ') WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
                                        ) ON [PRIMARY]'
                IF @ConstraintFields = 'ID'
                    SET @SqlConstraint = @SqlConstraint + ' TEXTIMAGE_ON [PRIMARY]'

                SET @SqlConstraint = @SqlConstraint + ';'

                IF @ConstraintFields IS NOT NULL
                    SET @SqlCreateTable = @SqlCreateTable + @SqlConstraint
                ELSE
                    SET @SqlCreateTable = @SqlCreateTable + ')'
            END
        ELSE
            BEGIN
                SET @SqlCreateTable = SUBSTRING(@SqlCreateTable, 1, LEN(@SqlCreateTable)-1) + ');'
            END
        
        PRINT @SqlCreateTable

        EXECUTE sp_executesql @SqlCreateTable
        CLOSE cursoreCampi
        DEALLOCATE cursoreCampi

        FETCH NEXT FROM cursoreNomeTabella INTO @NomeTabellaDaCreare, @ConstraintFields
    END
    CLOSE cursoreNomeTabella
    DEALLOCATE cursoreNomeTabella
    /* End of insertion of tables */

    /* This table will contain the views from which to start to create the new ones */
    CREATE TABLE #Views(
        ViewName varchar(50),
        ViewDefinition nvarchar(max)
    )

    /* Read the details of the views */
    SET @SqlString = N' SELECT  REPLACE(v.name, ' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ', ' + CONVERT(NVARCHAR(4),@ReferenceYear) + ') AS [ViewName],
                                REPLACE(m.definition, ' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ', ' + CONVERT(NVARCHAR(4),@ReferenceYear) + ') AS [ViewDefinition]
                        FROM    sys.views v JOIN sys.sql_modules m ON m.object_id = v.object_id
                        WHERE   v.name LIKE ''%' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + '''
                        ORDER BY ViewName;'
    PRINT @SqlString
    /* Insert the results in the view table */
    SET @SqlStringInsert = N'INSERT INTO #Views ' + @SqlString
    PRINT @SqlStringInsert
    EXECUTE sp_executesql @SqlStringInsert

    /* Start inserting views */
    DECLARE cursoreViste CURSOR FOR SELECT ViewName, ViewDefinition FROM #Views
    DECLARE @ViewName nvarchar(50)
    DECLARE @ViewDefinition nvarchar(max)
    OPEN cursoreViste
    FETCH NEXT FROM cursoreViste INTO @ViewName, @ViewDefinition
    WHILE @@FETCH_STATUS=0
    BEGIN
        SET @SqlCreateView = REPLACE(@ViewDefinition, 'CREATE','CREATE OR ALTER')
        PRINT @SqlCreateView
        EXECUTE sp_executesql @SqlCreateView
        FETCH NEXT FROM cursoreViste INTO @ViewName, @ViewDefinition
    END
    CLOSE cursoreViste
    DEALLOCATE cursoreViste

    /* End of insertion of views */
    
    /* This table will contain the list of constraints to add to the tables */
    CREATE TABLE #Constraints(
        TableName nvarchar(100),
        ConstraintName nvarchar(100),
        ConstraintColumn nvarchar(100),
        ConstraintDefinition nvarchar(20)
    )

    /* Read the data of the constraints */
    SET @SqlString = N' SELECT schema_name(t.schema_id) + ''.'' + REPLACE(t.[name], ' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ', ' + CONVERT(NVARCHAR(4),@ReferenceYear) + ') AS [TableName],
                            REPLACE(con.[name], ' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ', ' + CONVERT(NVARCHAR(4),@ReferenceYear) + ') AS [ConstraintName],
                            col.[name] AS [ConstraintColumn], con.[definition] AS [ConstraintDefinition]
                        FROM sys.default_constraints con
                            LEFT OUTER JOIN sys.objects t
                                ON con.parent_object_id = t.object_id
                            LEFT OUTER JOIN sys.all_columns col
                                ON con.parent_column_id = col.column_id
                                AND con.parent_object_id = col.object_id
                        WHERE t.name like ''%' + CONVERT(NVARCHAR(4),@ReferenceYear-1) + ''''

    /* Enter the results in the constraints table */
    SET @SqlStringInsert = N'INSERT INTO #Constraints ' + @SqlString
    PRINT @SqlStringInsert
    EXECUTE sp_executesql @SqlStringInsert
    
    /* Start creating constraints */
    DECLARE cursoreConstraints CURSOR FOR SELECT TableName, ConstraintName, ConstraintColumn, ConstraintDefinition FROM #Constraints
    DECLARE @TableName nvarchar(50)
    DECLARE @ConstraintName nvarchar(50)
    DECLARE @ConstraintColumn nvarchar(100)
    DECLARE @ConstraintDefinition varchar(20)
    OPEN cursoreConstraints
    FETCH NEXT FROM cursoreConstraints INTO @TableName, @ConstraintName, @ConstraintColumn, @ConstraintDefinition
    WHILE @@FETCH_STATUS=0
    BEGIN
        SET @SqlCreateConstraint = N'IF NOT EXISTS(SELECT * FROM sys.DEFAULT_CONSTRAINTS WHERE NAME=''' + @ConstraintName + ''' ) ALTER TABLE ' + @TableName + ' ADD CONSTRAINT [' + @ConstraintName + '] DEFAULT ' +  @ConstraintDefinition + ' FOR [' + @ConstraintColumn + ']' 
        PRINT @SqlCreateConstraint
        EXECUTE sp_executesql @SqlCreateConstraint
        FETCH NEXT FROM cursoreConstraints INTO @TableName, @ConstraintName, @ConstraintColumn, @ConstraintDefinition
    END
    CLOSE cursoreConstraints
    DEALLOCATE cursoreConstraints
    /* End of constraints creation */

    /* Delete the temporary tables */
    DROP TABLE #TableNamesToCreate
    DROP TABLE #Tables
    DROP TABLE #Views
    DROP TABLE #Constraints
END

问题现象

存储过程在大部分场景下运行正常，但原表中定义为float类型的字段，在新创建的备份表中会被自动转换为real类型。

问题原因

问题由两处逻辑错误共同导致：

字段类型生成逻辑错误（核心原因）
SQL Server中float类型的定义格式为float(n)，其中n是尾数精度，取值范围1-53：当n在1-24区间时，SQL Server自动将其映射为4字节单精度的real类型；当n在25-53区间时，才是8字节双精度的标准float类型。
现有脚本生成字段定义时，错误将字符串类型的长度规则套用到float类型上：脚本读取了sys.columns.max_length（存储字段占用的字节长度，标准float类型该值为8），直接拼接成float(8)，由于8落在1-24区间，SQL Server会自动将其解析为real类型。
同时脚本虽然读取了sys.columns.precision（存储float/decimal等类型的精度值）存入临时表，但全程未使用该字段生成定义。
元数据读取范围错误（隐患）
现有脚本读取列元数据时，WHERE条件匹配的是去掉_BCK_年份后缀的主表名，而非上一年度的备份表名，本身违背了“以上一年备份表为结构模板”的设计初衷，当主表和上一年备份表结构不一致时会出现更多结构偏差问题。

修复方案

需要修改两处逻辑：

修正列元数据读取逻辑
将读取列信息的SQL语句中WHERE条件改为直接匹配上一年度备份表，同时补充读取scale字段（供decimal/numeric等精确数值类型使用）：

首先修改#Tables临时表定义，添加ColumnScale int字段
将读取列的SQL中WHERE条件替换为：

WHERE t.name LIKE '%_BCK_' + CONVERT(NVARCHAR(4),@ReferenceYear-1)

SELECT子句中补充读取c.scale AS ColumnScale

修正字段定义生成逻辑
替换原有统一拼接长度的CASE逻辑，按数据类型分类处理参数拼接：

SET @SqlCreateField = @SqlCreateField + @ColumnName + ' [' + @ColumnType + ']'  
-- 按类型拼接对应参数
SET @SqlCreateField = @SqlCreateField + CASE
    -- 字符串类型
    WHEN @ColumnType IN ('char','varchar','nchar','nvarchar') THEN 
        CASE WHEN @ColumnLength = -1 THEN '(max) ' ELSE '(' + CONVERT(varchar(10),@ColumnLength) + ') ' END
    -- 精确数值类型
    WHEN @ColumnType IN ('decimal','numeric') THEN
        '('