Untitled

 avatar
unknown
plain_text
9 months ago
14 kB
2
Indexable
BEGIN TRY
    BEGIN TRANSACTION;

    DECLARE @table_name NVARCHAR(255);
    DECLARE @schema_name NVARCHAR(255);
    DECLARE @full_table_name NVARCHAR(511);
    DECLARE @column_name NVARCHAR(255);
    DECLARE @data_type NVARCHAR(255);
    DECLARE @is_nullable BIT;
    DECLARE @character_maximum_length INT;
    DECLARE @default_value NVARCHAR(MAX);
    DECLARE @object_id INT;
    DECLARE @constraint_name NVARCHAR(255);
    DECLARE @constraint_type NVARCHAR(255);
    DECLARE @constraint_columns NVARCHAR(MAX);
    DECLARE @referenced_table NVARCHAR(511);
    DECLARE @referenced_columns NVARCHAR(MAX);

    DECLARE @create_table NVARCHAR(MAX);
    DECLARE @alter_table NVARCHAR(MAX);
    DECLARE @newline CHAR(2) = CHAR(13) + CHAR(10);

    -- Get tables modified in the last 20 days
    DECLARE table_cursor CURSOR FOR
    SELECT t.object_id, s.name AS schema_name, t.name AS table_name
    FROM sys.tables t
    JOIN sys.schemas s ON t.schema_id = s.schema_id
    WHERE t.modify_date >= DATEADD(DAY, -20, GETDATE())
    ORDER BY s.name, t.name;

    OPEN table_cursor;

    FETCH NEXT FROM table_cursor INTO @object_id, @schema_name, @table_name;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @full_table_name = '[' + @schema_name + '].[' + @table_name + ']';

        SET @create_table = 'IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ''' + @schema_name + ''' AND TABLE_NAME = ''' + @table_name + ''')' + @newline
                          + 'BEGIN' + @newline
                          + '    CREATE TABLE ' + @full_table_name + '(' + @newline;

        -- Get columns for the current table
        DECLARE column_cursor CURSOR FOR
        SELECT c.name AS column_name, t.name AS data_type, c.is_nullable, c.max_length, dc.definition
        FROM sys.columns c
        JOIN sys.types t ON c.user_type_id = t.user_type_id
        LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
        WHERE c.object_id = @object_id
        ORDER BY c.column_id;

        OPEN column_cursor;

        FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @is_nullable, @character_maximum_length, @default_value;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SET @create_table = @create_table + '        [' + @column_name + '] ' + @data_type;

            IF @data_type IN ('char', 'varchar', 'nchar', 'nvarchar')
            BEGIN
                IF @character_maximum_length = -1
                BEGIN
                    SET @create_table = @create_table + '(max)';
                END
                ELSE
                BEGIN
                    SET @create_table = @create_table + '(' + CAST(@character_maximum_length AS NVARCHAR) + ')';
                END
            END

            IF @is_nullable = 0
            BEGIN
                SET @create_table = @create_table + ' NOT NULL';
            END
            ELSE
            BEGIN
                SET @create_table = @create_table + ' NULL';
            END

            IF @default_value IS NOT NULL
            BEGIN
                SET @create_table = @create_table + ' DEFAULT ' + @default_value;
            END

            SET @create_table = @create_table + ',' + @newline;

            FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @is_nullable, @character_maximum_length, @default_value;
        END;

        CLOSE column_cursor;
        DEALLOCATE column_cursor;

        -- Remove the last comma
        SET @create_table = LEFT(@create_table, LEN(@create_table) - 3) + @newline;
        SET @create_table = @create_table + '    );' + @newline;
        SET @create_table = @create_table + 'END' + @newline;

        PRINT @create_table;

        -- Handle constraints (primary key, foreign key, unique, check)
        DECLARE constraint_cursor CURSOR FOR
        SELECT con.name AS constraint_name, con.type_desc AS constraint_type
        FROM sys.objects con
        JOIN sys.objects t ON con.parent_object_id = t.object_id
        WHERE t.object_id = @object_id
        AND con.type IN ('PK', 'F', 'UQ', 'C')
        ORDER BY con.name;

        OPEN constraint_cursor;

        FETCH NEXT FROM constraint_cursor INTO @constraint_name, @constraint_type;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            IF @constraint_type = 'PRIMARY_KEY_CONSTRAINT'
            BEGIN
                IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = @table_name AND CONSTRAINT_NAME = @constraint_name)
                BEGIN
                    SET @constraint_columns = (SELECT STRING_AGG('[' + c.name + ']', ', ')
                                               FROM sys.index_columns ic
                                               JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                                               WHERE ic.object_id = @object_id AND ic.index_id = (SELECT index_id FROM sys.indexes WHERE object_id = @object_id AND is_primary_key = 1));
                    PRINT 'ALTER TABLE ' + @full_table_name + ' ADD CONSTRAINT [' + @constraint_name + '] PRIMARY KEY (' + @constraint_columns + ');';
                END
            END
            ELSE IF @constraint_type = 'FOREIGN_KEY_CONSTRAINT'
            BEGIN
                IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = @table_name AND CONSTRAINT_NAME = @constraint_name)
                BEGIN
                    SET @constraint_columns = (SELECT STRING_AGG('[' + c.name + ']', ', ')
                                               FROM sys.foreign_key_columns fkc
                                               JOIN sys.columns c ON fkc.parent_object_id = c.object_id AND fkc.parent_column_id = c.column_id
                                               WHERE fkc.constraint_object_id = (SELECT object_id FROM sys.foreign_keys WHERE parent_object_id = @object_id AND name = @constraint_name));
                    SET @referenced_table = (SELECT '[' + OBJECT_SCHEMA_NAME(referenced_object_id) + '].[' + OBJECT_NAME(referenced_object_id) + ']' 
                                             FROM sys.foreign_keys 
                                             WHERE parent_object_id = @object_id AND name = @constraint_name);
                    SET @referenced_columns = (SELECT STRING_AGG('[' + c.name + ']', ', ')
                                               FROM sys.foreign_key_columns fkc
                                               JOIN sys.columns c ON fkc.referenced_object_id = c.object_id AND fkc.referenced_column_id = c.column_id
                                               WHERE fkc.constraint_object_id = (SELECT object_id FROM sys.foreign_keys WHERE parent_object_id = @object_id AND name = @constraint_name));
                    PRINT 'ALTER TABLE ' + @full_table_name + ' ADD CONSTRAINT [' + @constraint_name + '] FOREIGN KEY (' + @constraint_columns + ') REFERENCES ' + @referenced_table + ' (' + @referenced_columns + ');';
                END
            END
            ELSE IF @constraint_type = 'UNIQUE_CONSTRAINT'
            BEGIN
                IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = @table_name AND CONSTRAINT_NAME = @constraint_name)
                BEGIN
                    SET @constraint_columns = (SELECT STRING_AGG('[' + c.name + ']', ', ')
                                               FROM sys.index_columns ic
                                               JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                                               WHERE ic.object_id = @object_id AND ic.index_id = (SELECT index_id FROM sys.indexes WHERE object_id = @object_id AND is_unique = 1 AND name = @constraint_name));
                    PRINT 'ALTER TABLE ' + @full_table_name + ' ADD CONSTRAINT [' + @constraint_name + '] UNIQUE (' + @constraint_columns + ');';
                END
            END
            ELSE IF @constraint_type = 'CHECK_CONSTRAINT'
            BEGIN
                IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = @table_name AND CONSTRAINT_NAME = @constraint_name)
                BEGIN
                    SET @constraint_columns = (SELECT definition FROM sys.check_constraints WHERE object_id = (SELECT object_id FROM sys.check_constraints WHERE parent_object_id = @object_id AND name = @constraint_name));
                    PRINT 'ALTER TABLE ' + @full_table_name + ' ADD CONSTRAINT [' + @constraint_name + '] CHECK ' + @constraint_columns + ';';
                END
            END

            FETCH NEXT FROM constraint_cursor INTO @constraint_name, @constraint_type;
        END;

        CLOSE constraint_cursor;
        DEALLOCATE constraint_cursor;

        -- Synchronize column definitions
        DECLARE column_cursor CURSOR FOR
        SELECT c.name AS column_name, t.name AS data_type, c.is_nullable, c.max_length, dc.definition
        FROM sys.columns c
        JOIN sys.types t ON c.user_type_id = t.user_type_id
        LEFT JOIN sys.default_constraints dc ON c.default_object_id = dc.object_id
        WHERE c.object_id = @object_id
        ORDER BY c.column_id;

        OPEN column_cursor;

        FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @is_nullable, @character_maximum_length, @default_value;

        WHILE @@FETCH_STATUS = 0
        BEGIN
            DECLARE @existing_data_type NVARCHAR(255);
            DECLARE @existing_is_nullable BIT;
            DECLARE @existing_character_maximum_length INT;
            DECLARE @existing_default_value NVARCHAR(MAX);

            SELECT @existing_data_type = DATA_TYPE, 
                   @existing_is_nullable = CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END, 
                   @existing_character_maximum_length = CHARACTER_MAXIMUM_LENGTH,
                   @existing_default_value = COLUMN_DEFAULT
            FROM INFORMATION_SCHEMA.COLUMNS
            WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = @table_name AND COLUMN_NAME = @column_name;

            IF @existing_data_type IS NULL
            BEGIN
                -- Column does not exist, add it
                SET @alter_table = 'ALTER TABLE ' + @full_table_name + ' ADD [' + @column_name + '] ' + @data_type;

                IF @data_type IN ('char', 'varchar', 'nchar', 'nvarchar')
                BEGIN
                    IF @character_maximum_length = -1
                    BEGIN
                        SET @alter_table = @alter_table + '(max)';
                    END
                    ELSE
                    BEGIN
                        SET @alter_table = @alter_table + '(' + CAST(@character_maximum_length AS NVARCHAR) + ')';
                    END
                END

                IF @is_nullable = 0
                BEGIN
                    -- If the column is not nullable, add a default value if it exists
                    IF @default_value IS NOT NULL
                    BEGIN
                        SET @alter_table = @alter_table + ' NOT NULL DEFAULT ' + @default_value;
                    END
                    ELSE
                    BEGIN
                        -- Otherwise, make it nullable to avoid issues
                        SET @alter_table = @alter_table + ' NULL';
                    END
                END
                ELSE
                BEGIN
                    SET @alter_table = @alter_table + ' NULL';
                END

                SET @alter_table = @alter_table + ';' + @newline;

                PRINT @alter_table;
            END
            ELSE
            BEGIN
                -- Column exists, check if it needs to be modified
                IF @existing_data_type != @data_type OR @existing_is_nullable != @is_nullable OR @existing_character_maximum_length != @character_maximum_length OR @existing_default_value != @default_value
                BEGIN
                    SET @alter_table = 'ALTER TABLE ' + @full_table_name + ' ALTER COLUMN [' + @column_name + '] ' + @data_type;

                    IF @data_type IN ('char', 'varchar', 'nchar', 'nvarchar')
                    BEGIN
                        IF @character_maximum_length = -1
                        BEGIN
                            SET @alter_table = @alter_table + '(max)';
                        END
                        ELSE
                        BEGIN
                            SET @alter_table = @alter_table + '(' + CAST(@character_maximum_length AS NVARCHAR) + ')';
                        END
                    END

                    IF @is_nullable = 0
                    BEGIN
                        SET @alter_table = @alter_table + ' NOT NULL';
                    END
                    ELSE
                    BEGIN
                        SET @alter_table = @alter_table + ' NULL';
                    END

                    SET @alter_table = @alter_table + ';' + @newline;

                    PRINT @alter_table;
                END
            END

            FETCH NEXT FROM column_cursor INTO @column_name, @data_type, @is_nullable, @character_maximum_length, @default_value;
        END;

        CLOSE column_cursor;
        DEALLOCATE column_cursor;

        FETCH NEXT FROM table_cursor INTO @object_id, @schema_name, @table_name;
    END;

    CLOSE table_cursor;
    DEALLOCATE table_cursor;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT 'Error occurred: ' + ERROR_MESSAGE();
END CATCH;
Editor is loading...
Leave a Comment