sql-servert-sqltypesautomation

Duplicate table structure dynamically - how to manage float datatype


I'm working on a DB that manages history tables in the following way: the "dashboard" table called, for example, MYTABLE1, and an increasing number of "copies", one for each year: MYTABLE1_BCK_2014, MYTABLE1_BCK_2015,..., MYTABLE1_BCK_2022, and so on. Same way for MYTABLE2, MYTABLE3, etc.

Then I can have N tables to duplicate each year, and the copy is made from the last backup table existing (for example, MYTABLE_BCK_2022 is created copying the structure from MYTABLE_BCK_2021, due to, along the time, the dashboard table could be changed in the structure, but not always the system needs to reflect this change on the backup table). At worst, when the dashboard table changes its structure, only the last backup table structure is updated.

Obviously, for each dashboard table, I can have a variable number of fields and field types, including float, nvarchar, varchar, datetime, etc.

I have written a script that dynamically detects which tables need to be cloned, creates a copy of each, and adds any constraint needed in this way:

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

The script is working almost well, but there is an issue with float datatype fields, that are automatically converted in real datatype fields.

Does anyone know why this is happening and/or how to manage it?


Solution

  • As suggested by @SMor, I would mistrust assigning a precision (@ColumnLengthToChar) to the float datatype. It's legit syntax, but not necessary and might be causing problems? Try adding it (and "real") to this NOT IN list:

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

    Also, see what happens when you collect the PRINT statements of your dynamic SQL and run them manually.