Untitled
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