SqlServer-動態復制表結構-如何管理浮點資料型別-有解無憂

我正在開發一個以下列方式管理歷史表的資料庫：例如名為 MYTABLE1 的“儀表板”表，以及越來越多的“副本”，每年一個：MYTABLE1_BCK_2014，MYTABLE1_BCK_2015，...， MYTABLE1_BCK_2022，依此類推。MYTABLE2、MYTABLE3 等的方法相同。

然后我可以每年復制 N 個表，并從現有的最后一個備份表制作副本（例如，MYTABLE_BCK_2022 是從 MYTABLE_BCK_2021 復制結構創建的，因為隨著時間的推移，儀表板表可以在結構，但系統并不總是需要在備份表上反映此更改）。在最壞的情況下，當儀表板表更改其結構時，僅更新最后一個備份表結構。

顯然，對于每個儀表板表，我可以擁有可變數量的欄位和欄位型別，包括 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

該腳本運行良好，但浮點資料型別欄位存在問題，該欄位會自動轉換為實際資料型別欄位。

有誰知道為什么會發生這種情況和/或如何管理它？

uj5u.com熱心網友回復：

正如@SMor 所建議的那樣，我不相信將精度（@ColumnLengthToChar）分配給浮點資料型別。這是合法的語法，但不是必需的并且可能會導致問題？嘗試將它（和“真實”）添加到此 NOT IN 串列中：

SET @SqlCreateField = @SqlCreateField   CASE WHEN @ColumnType NOT IN ('int','bit','bigint','date','datetime','datetime2','ntext') THEN '('   @ColumnLengthToChar   ') ' ELSE ' ' END

此外，看看當您收集動態 SQL 的 PRINT 陳述句并手動運行它們時會發生什么。

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/486992.html

標籤：sql服务器 tsql 类型自动化

上一篇：`End`附近的語法不正確

下一篇：如何使用C#在SQLServer中僅在第一行插入所有文本值