ป้ายกำกับ: Table

-- USE DB_DEV;
-- USE DB_QA;
-- USE DB_PROD; SET NOCOUNT ON; BEGIN TRY
BEGIN TRANSACTION -- ============================================= -- 1. CONFIGURATION -- ============================================= DECLARE @SchemaName NVARCHAR (128) = '' DECLARE @TableName NVARCHAR (128) = 'wp_posts' -- Table Description DECLARE @TableDesc NVARCHAR (4000) = N'สำหรับเก็บ post'; -- Check for NULL or Empty Schema -> Default to 'dbo' IF @SchemaName IS NULL OR LTRIM (RTRIM (@SchemaName)) = '' SET @SchemaName = 'dbo'; -- Standard Variables DECLARE @TempTableName NVARCHAR (128) = @TableName + '_dup' DECLARE @OldTableName NVARCHAR (128) = @TableName + '_old' DECLARE @TargetObjID INT = OBJECT_ID (QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TableName)) -- Dynamic SQL Variables DECLARE @SqlDropFK NVARCHAR (MAX) = '', @SqlCreateFK NVARCHAR (MAX) = '', @SqlCreatePK_Indexes NVARCHAR (MAX) = '' DECLARE @CreateTableSQL NVARCHAR (MAX) , @CopySQL NVARCHAR (MAX) , @DropOldSQL NVARCHAR (MAX) DECLARE @ColList NVARCHAR (MAX) = '', @SharedColList NVARCHAR (MAX) = '', @Msg NVARCHAR (MAX) DECLARE @IdentityColName NVARCHAR (128) = NULL DECLARE @PreserveIdentity BIT = 0 DECLARE @CurrentFullName NVARCHAR (256) , @TempFullName NVARCHAR (256) -- Cursor Helpers DECLARE @Cur_ColName NVARCHAR (128) , @Cur_DataType NVARCHAR (100) , @Cur_IsNullable CHAR (1) , @Cur_Desc NVARCHAR (4000) DECLARE @Cur_FKID INT, @Cur_FKName NVARCHAR (128) , @Cur_ParentSchema NVARCHAR (128) , @Cur_ParentTable NVARCHAR (128) DECLARE @Helper_FKCols NVARCHAR (MAX) , @Helper_RefCols NVARCHAR (MAX) DECLARE @Cur_IdxID INT, @Cur_IdxName NVARCHAR (128) , @Cur_IsPK BIT, @Cur_IsUnique BIT, @Cur_IdxType TINYINT DECLARE @Helper_IdxCols NVARCHAR (MAX) , @Helper_IncCols NVARCHAR (MAX) -- ============================================= -- 2. DEFINE NEW STRUCTURE -- ============================================= DECLARE @ColumnDefs TABLE (ID INT IDENTITY (1,1) , ColumnName NVARCHAR (128) , DataType NVARCHAR (100) , IsNullable CHAR (1) , -- 'Y' = NULL, 'N' = NOT NULL Description NVARCHAR (4000)) ; -- Insert Columns INSERT INTO @ColumnDefs (ColumnName, DataType, IsNullable, Description) VALUES ('ID', 'BIGINT IDENTITY (1,1) ', 'N', 'Primary Key') , ('post_author', 'BIGINT', 'N', 'Author ID') , ('post_date', 'DATETIME2 (0) ', 'N', 'Post Date') , ('post_date_gmt', 'DATETIME2 (0) ', 'N', 'Post Date GMT') , ('post_content', 'NVARCHAR (MAX) ', 'N', 'Post Content') , ('post_title', 'NVARCHAR (MAX) ', 'N', 'Post Title') , ('post_excerpt', 'NVARCHAR (MAX) ', 'N', 'Post Excerpt') , ('post_status', 'NVARCHAR (20) ', 'N', 'Post Status (publish, draft, etc.) ') , ('comment_status', 'NVARCHAR (20) ', 'N', 'Comment Status (open/closed) ') , ('ping_status', 'NVARCHAR (20) ', 'N', 'Ping Status') , ('post_password', 'NVARCHAR (255) ', 'N', 'Post Password') , ('post_name', 'NVARCHAR (200) ', 'N', 'Post Name (Slug) ') , ('to_ping', 'NVARCHAR (MAX) ', 'N', 'URLs to ping') , ('pinged', 'NVARCHAR (MAX) ', 'N', 'Pinged URLs') , ('post_modified', 'DATETIME2 (0) ', 'N', 'Modification Date') , ('post_modified_gmt', 'DATETIME2 (0) ', 'N', 'Modification Date GMT') , ('post_content_filtered', 'NVARCHAR (MAX) ', 'N', 'Filtered Content') , ('post_parent', 'BIGINT', 'N', 'Parent Post ID') , ('guid', 'NVARCHAR (255) ', 'N', 'Global Unique ID') , ('menu_order', 'INT', 'N', 'Menu Order') , ('post_type', 'NVARCHAR (20) ', 'N', 'Post Type (post, page, etc.) ') , ('post_mime_type', 'NVARCHAR (100) ', 'N', 'Mime Type') , ('comment_count', 'BIGINT', 'N', 'Comment Count') ; -- ============================================= -- 2.1 AUTO-DISCOVERY & IDENTITY CHECK -- ============================================= -- Detect Identity Column Name in New Structure SELECT TOP 1 @IdentityColName = ColumnName FROM @ColumnDefs WHERE DataType LIKE '%IDENTITY%'; IF @TargetObjID IS NOT NULL BEGIN PRINT '... Auto-detecting missing columns from existing table ...' INSERT INTO @ColumnDefs (ColumnName, DataType, IsNullable, Description) SELECT c.name, CASE WHEN t.name IN ('varchar', 'char', 'varbinary', 'binary') THEN t.name + ' (' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST (c.max_length AS VARCHAR) END + ') ' WHEN t.name IN ('nvarchar', 'nchar') THEN t.name + ' (' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST (c.max_length / 2 AS VARCHAR) END + ') ' WHEN t.name IN ('decimal', 'numeric') THEN t.name + ' (' + CAST (c.precision AS VARCHAR) + ',' + CAST (c.scale AS VARCHAR) + ') ' ELSE t.name END, CASE WHEN c.is_nullable = 1 THEN 'Y' ELSE 'N' END, ISNULL (CAST (ep.value AS NVARCHAR (4000)) , '') FROM sys.columns c JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT JOIN sys.extended_properties ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description' WHERE c.object_id = @TargetObjID AND c.is_computed = 0 AND c.name NOT IN (SELECT ColumnName FROM @ColumnDefs) ORDER BY c.column_id; PRINT ' -> Auto-discovery complete.' END -- ============================================= -- 3. BACKUP RELATIONS -- ============================================= IF @TargetObjID IS NOT NULL BEGIN PRINT '... Backing up Relations/Indexes ...' -- FKs DECLARE FKCursor CURSOR LOCAL FAST_FORWARD FOR SELECT fk.object_id, fk.name, SCHEMA_NAME (fk.schema_id) , OBJECT_NAME (fk.parent_object_id) FROM sys.foreign_keys fk WHERE fk.referenced_object_id = @TargetObjID; OPEN FKCursor; FETCH NEXT FROM FKCursor INTO @Cur_FKID, @Cur_FKName, @Cur_ParentSchema, @Cur_ParentTable; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Helper_FKCols = STUFF ( (SELECT ',' + QUOTENAME (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 = @Cur_FKID ORDER BY fkc.constraint_column_id FOR XML PATH ('')) , 1, 1, '') ; SELECT @Helper_RefCols = STUFF ( (SELECT ',' + QUOTENAME (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 = @Cur_FKID ORDER BY fkc.constraint_column_id FOR XML PATH ('')) , 1, 1, '') ; SET @SqlDropFK += 'ALTER TABLE ' + QUOTENAME (@Cur_ParentSchema) + '.' + QUOTENAME (@Cur_ParentTable) + ' DROP CONSTRAINT ' + QUOTENAME (@Cur_FKName) + ';' + CHAR (13) ; SET @SqlCreateFK += 'ALTER TABLE ' + QUOTENAME (@Cur_ParentSchema) + '.' + QUOTENAME (@Cur_ParentTable) + ' WITH NOCHECK ADD CONSTRAINT ' + QUOTENAME (@Cur_FKName) + ' FOREIGN KEY (' + @Helper_FKCols + ') REFERENCES ' + QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TableName) + ' (' + @Helper_RefCols + ') ;' + CHAR (13) ; FETCH NEXT FROM FKCursor INTO @Cur_FKID, @Cur_FKName, @Cur_ParentSchema, @Cur_ParentTable; END CLOSE FKCursor; DEALLOCATE FKCursor; -- Indexes DECLARE IdxCursor CURSOR LOCAL FAST_FORWARD FOR SELECT index_id, name, is_primary_key, is_unique, type FROM sys.indexes WHERE object_id = @TargetObjID AND type IN (1, 2) ORDER BY is_primary_key DESC; OPEN IdxCursor; FETCH NEXT FROM IdxCursor INTO @Cur_IdxID, @Cur_IdxName, @Cur_IsPK, @Cur_IsUnique, @Cur_IdxType; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @Helper_IdxCols = STUFF ( (SELECT ',' + QUOTENAME (c.name) + CASE WHEN ic.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END 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 = @TargetObjID AND ic.index_id = @Cur_IdxID AND ic.is_included_column = 0 ORDER BY ic.key_ordinal FOR XML PATH ('')) , 1, 1, '') ; SELECT @Helper_IncCols = STUFF ( (SELECT ',' + QUOTENAME (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 = @TargetObjID AND ic.index_id = @Cur_IdxID AND ic.is_included_column = 1 ORDER BY ic.column_id FOR XML PATH ('')) , 1, 1, '') ; IF @Cur_IsPK = 1 SET @SqlCreatePK_Indexes += 'ALTER TABLE ' + QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TableName) + ' ADD CONSTRAINT ' + QUOTENAME (@Cur_IdxName) + ' PRIMARY KEY ' + CASE WHEN @Cur_IdxType = 1 THEN 'CLUSTERED' ELSE 'NONCLUSTERED' END + ' (' + @Helper_IdxCols + ') ;' + CHAR (13) ; ELSE SET @SqlCreatePK_Indexes += 'CREATE ' + CASE WHEN @Cur_IsUnique = 1 THEN 'UNIQUE ' ELSE '' END + 'NONCLUSTERED INDEX ' + QUOTENAME (@Cur_IdxName) + ' ON ' + QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TableName) + ' (' + @Helper_IdxCols + ') ' + CASE WHEN LEN (@Helper_IncCols) > 0 THEN ' INCLUDE (' + @Helper_IncCols + ') ' ELSE '' END + ';' + CHAR (13) ; FETCH NEXT FROM IdxCursor INTO @Cur_IdxID, @Cur_IdxName, @Cur_IsPK, @Cur_IsUnique, @Cur_IdxType; END CLOSE IdxCursor; DEALLOCATE IdxCursor; END -- ============================================= -- 4. BUILD SCRIPTS & CHECK SHARED IDENTITY -- ============================================= DECLARE ScriptCursor CURSOR LOCAL FAST_FORWARD FOR SELECT ColumnName, DataType, IsNullable FROM @ColumnDefs ORDER BY ID; OPEN ScriptCursor; FETCH NEXT FROM ScriptCursor INTO @Cur_ColName, @Cur_DataType, @Cur_IsNullable; WHILE @@FETCH_STATUS = 0 BEGIN -- Build Create List IF LEN (@ColList) > 0 SET @ColList += ', ' + CHAR (13) + CHAR (10) + ' '; SET @ColList += QUOTENAME (@Cur_ColName) + ' ' + @Cur_DataType + CASE WHEN @Cur_IsNullable = 'Y' THEN ' NULL' ELSE ' NOT NULL' END; -- Build Shared Column List IF @TargetObjID IS NOT NULL AND EXISTS (SELECT 1 FROM sys.columns WHERE object_id = @TargetObjID AND name = @Cur_ColName) BEGIN IF LEN (@SharedColList) > 0 SET @SharedColList += ', '; SET @SharedColList += QUOTENAME (@Cur_ColName) ; -- [KEY FIX] If this shared column is the Identity Column, mark flag to Preserve Identity IF @IdentityColName IS NOT NULL AND @Cur_ColName = @IdentityColName BEGIN SET @PreserveIdentity = 1; END END FETCH NEXT FROM ScriptCursor INTO @Cur_ColName, @Cur_DataType, @Cur_IsNullable; END CLOSE ScriptCursor; DEALLOCATE ScriptCursor; -- ============================================= -- 5. EXECUTION -- ============================================= -- 5.1 Create Temp Table SET @CreateTableSQL = 'CREATE TABLE ' + QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TempTableName) + ' (' + @ColList + ') ;'; PRINT 'Creating Temp Table...' EXEC sp_executesql @CreateTableSQL; -- 5.2 Copy Data IF @TargetObjID IS NOT NULL AND LEN (@SharedColList) > 0 BEGIN IF LEN (@SqlDropFK) > 0 EXEC sp_executesql @SqlDropFK; PRINT 'Copying data...' SET @CopySQL = ''; -- Only set IDENTITY_INSERT ON if we are actually copying the Identity Column IF @PreserveIdentity = 1 BEGIN SET @CopySQL += 'SET IDENTITY_INSERT ' + QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TempTableName) + ' ON; '; END SET @CopySQL += 'INSERT INTO ' + QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TempTableName) + ' (' + @SharedColList + ') ' + 'SELECT ' + @SharedColList + ' FROM ' + QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TableName) + '; '; IF @PreserveIdentity = 1 BEGIN SET @CopySQL += 'SET IDENTITY_INSERT ' + QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TempTableName) + ' OFF; '; END EXEC sp_executesql @CopySQL; END -- 5.3 Column Descriptions DECLARE DescCursor CURSOR LOCAL FAST_FORWARD FOR SELECT ColumnName, Description FROM @ColumnDefs WHERE Description IS NOT NULL AND Description <> ''; OPEN DescCursor; FETCH NEXT FROM DescCursor INTO @Cur_ColName, @Cur_Desc; WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_addextendedproperty @name = N'MS_Description', @value = @Cur_Desc, @level0type = N'SCHEMA', @level0name = @SchemaName, @level1type = N'TABLE', @level1name = @TempTableName, @level2type = N'COLUMN', @level2name = @Cur_ColName; FETCH NEXT FROM DescCursor INTO @Cur_ColName, @Cur_Desc; END CLOSE DescCursor; DEALLOCATE DescCursor; -- 5.4 Table Description PRINT 'Adding Table Description...' EXEC sp_addextendedproperty @name = N'MS_Description', @value = @TableDesc, @level0type = N'SCHEMA', @level0name = @SchemaName, @level1type = N'TABLE', @level1name = @TempTableName; -- 5.5 Swap Tables IF @TargetObjID IS NOT NULL BEGIN PRINT 'Swapping tables...' SET @CurrentFullName = QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TableName) ; SET @TempFullName = QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TempTableName) ; EXEC sp_rename @CurrentFullName, @OldTableName; EXEC sp_rename @TempFullName, @TableName; SET @DropOldSQL = 'DROP TABLE ' + QUOTENAME (@SchemaName) + '.' + QUOTENAME (@OldTableName) + ';'; EXEC sp_executesql @DropOldSQL; IF LEN (@SqlCreatePK_Indexes) > 0 EXEC sp_executesql @SqlCreatePK_Indexes; IF LEN (@SqlCreateFK) > 0 EXEC sp_executesql @SqlCreateFK; END ELSE BEGIN SET @TempFullName = QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TempTableName) ; EXEC sp_rename @TempFullName, @TableName; END COMMIT TRANSACTION
PRINT 'Migration Completed Successfully.' END TRY
BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; SET @Msg = 'ERROR: ' + ERROR_MESSAGE () + ' (Line: ' + CAST (ERROR_LINE () AS NVARCHAR (10)) + ') '; PRINT @Msg;
END CATCH;