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