SQL Server: ย้าย column ในตาราง

/**************************************************************************************************
	Script to Reorder Columns (Final Fixed: PK Name Collision Handled) Fixes:
	1. Renames the OLD PK on the backup table first to free up the name.
	2. Handles Identity Check.
	3. Fully compatible syntax.
**************************************************************************************************/

-- USE DB_DEV;
-- USE DB_QA;
-- USE DB_PROD;

-- ==================== Edit only these 3 lines ====================
DECLARE @SchemaName NVARCHAR (128) = N'FINANCE';
DECLARE @TableName NVARCHAR (128) = N'TEMP_AR_ISSUE_DNCN';

-- Enter the column names to be ordered first
DECLARE @DesiredColumnOrder NVARCHAR (MAX) = N'
PP_ID,
PP_CREATE_BY,
PP_CREATE_DT,
PP_MODIFY_BY,
PP_MODIFY_DT,
PP_TIMESTAMP,

PP_TITLE,
PP_CONTENT
';
-- =================================================================

IF @SchemaName IS NULL OR @SchemaName = N'' SET @SchemaName = N'dbo';

SET NOCOUNT ON;
DECLARE @NewTable	 NVARCHAR (258) = QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TableName + '_New') ;
DECLARE @OldTable	 NVARCHAR (258) = QUOTENAME (@SchemaName) + '.' + QUOTENAME (@TableName) ;
DECLARE @SQL		 NVARCHAR (MAX) = N'';
DECLARE @DropFK		 NVARCHAR (MAX) = N'';
DECLARE @CreateFK	 NVARCHAR (MAX) = N'';
DECLARE @ColList	 NVARCHAR (MAX) = N'';
DECLARE @PKCols		 NVARCHAR (MAX) = N'';
DECLARE @HasIdentity BIT = 0;

-- Clean spaces AND Newlines (CR/LF) SET @DesiredColumnOrder = REPLACE (@DesiredColumnOrder, ' ', '') ;
SET @DesiredColumnOrder = REPLACE (@DesiredColumnOrder, CHAR (13) , '') ; -- Remove Carriage Return
SET @DesiredColumnOrder = REPLACE (@DesiredColumnOrder, CHAR (10) , '') ; -- Remove Line Feed

-- 1. Get FK Scripts
SELECT
	@DropFK = @DropFK + N'ALTER TABLE ' + QUOTENAME (OBJECT_SCHEMA_NAME (parent_object_id)) + '.' + QUOTENAME (OBJECT_NAME (parent_object_id)) + N' DROP CONSTRAINT ' + QUOTENAME (fk.name) + N';' + CHAR (10) ,
	@CreateFK = @CreateFK + N'ALTER TABLE ' + QUOTENAME (OBJECT_SCHEMA_NAME (parent_object_id)) + '.' + QUOTENAME (OBJECT_NAME (parent_object_id)) + N' WITH CHECK ADD CONSTRAINT ' + QUOTENAME (fk.name) + N' FOREIGN KEY ('
				+ STUFF ( (SELECT ',' + QUOTENAME (COL_NAME (fc2.parent_object_id, fc2.parent_column_id)) FROM sys.foreign_key_columns fc2
							WHERE fc2.constraint_object_id = fk.object_id
							ORDER BY fc2.constraint_column_id
							FOR XML PATH ('')) ,1,1,'') + N') REFERENCES '
				+ QUOTENAME (OBJECT_SCHEMA_NAME (referenced_object_id)) + '.' + QUOTENAME (OBJECT_NAME (referenced_object_id)) + N' ('
				+ STUFF ( (SELECT ',' + QUOTENAME (COL_NAME (fc2.referenced_object_id, fc2.referenced_column_id)) FROM sys.foreign_key_columns fc2
							WHERE fc2.constraint_object_id = fk.object_id
							ORDER BY fc2.constraint_column_id
							FOR XML PATH ('')) ,1,1,'') + N') ;' + CHAR (10) + N'ALTER TABLE ' + QUOTENAME (OBJECT_SCHEMA_NAME (parent_object_id)) + '.' + QUOTENAME (OBJECT_NAME (parent_object_id)) + N' CHECK CONSTRAINT ' + QUOTENAME (fk.name) + N';' + CHAR (10) FROM sys.foreign_keys fk
WHERE referenced_object_id = OBJECT_ID (@OldTable) OR parent_object_id = OBJECT_ID (@OldTable) ;

-- 2. Begin Transaction
BEGIN TRANSACTION;
BEGIN TRY

	-- Drop FKs
	IF LEN (@DropFK) > 0 EXEC sp_executesql @DropFK;

	-- 3. Prepare Columns Data
	DECLARE @ColsTable TABLE (ColName	 NVARCHAR (128) ,
		DataType	NVARCHAR (128) ,
		IsNullable VARCHAR (3) ,
		ColDefault NVARCHAR (MAX) ,
		IsIdentity BIT,
		IsComputed BIT,
		ComputedDef NVARCHAR (MAX) ,
		IsPersisted BIT,
		SortOrder INT) ;

	INSERT INTO @ColsTable
	SELECT
		c.COLUMN_NAME,
		DATA_TYPE =
				CASE
					WHEN c.DATA_TYPE IN ('varchar','char','nvarchar','nchar','varbinary','binary') AND c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + ' (MAX) '
					WHEN c.DATA_TYPE IN ('varchar','char','nvarchar','nchar','varbinary','binary') THEN c.DATA_TYPE + ' (' + CASE WHEN c.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX' ELSE CAST (c.CHARACTER_MAXIMUM_LENGTH AS VARCHAR (10)) END + ') '
					WHEN c.DATA_TYPE IN ('decimal','numeric') THEN c.DATA_TYPE + ' (' + CAST (c.NUMERIC_PRECISION AS VARCHAR (10)) + ',' + CAST (c.NUMERIC_SCALE AS VARCHAR (10)) + ') '
					WHEN c.DATA_TYPE IN ('float','real') THEN c.DATA_TYPE + CASE WHEN c.NUMERIC_PRECISION IS NULL THEN '' ELSE ' (' + CAST (c.NUMERIC_PRECISION AS VARCHAR (10)) + ') ' END
					WHEN c.DATA_TYPE = 'datetime2' THEN c.DATA_TYPE + ' (' + CAST (c.DATETIME_PRECISION AS VARCHAR (10)) + ') '
					ELSE c.DATA_TYPE
				END,
		IS_NULLABLE = c.IS_NULLABLE,
		COLUMN_DEFAULT = c.COLUMN_DEFAULT,
		IS_IDENTITY = CASE WHEN ic.column_id IS NOT NULL THEN 1 ELSE 0 END,
		IS_COMPUTED = CASE WHEN cc.definition IS NOT NULL THEN 1 ELSE 0 END,
		COMPUTED_DEF = cc.definition,
		IS_PERSISTED = cc.is_persisted,
		SortOrder = CASE
							WHEN CHARINDEX (',' + c.COLUMN_NAME + ',', ',' + @DesiredColumnOrder + ',') > 0
							THEN CHARINDEX (',' + c.COLUMN_NAME + ',', ',' + @DesiredColumnOrder + ',') ELSE 100000 + c.ORDINAL_POSITION
					END
	FROM INFORMATION_SCHEMA.COLUMNS c
	LEFT JOIN sys.identity_columns ic ON OBJECT_ID (@OldTable) = ic.object_id AND c.COLUMN_NAME = ic.name
	LEFT JOIN sys.computed_columns cc ON OBJECT_ID (@OldTable) = cc.object_id AND c.COLUMN_NAME = cc.name
	WHERE c.TABLE_SCHEMA = @SchemaName AND c.TABLE_NAME = @TableName;

	-- Check Identity
	IF EXISTS (SELECT 1 FROM @ColsTable WHERE IsIdentity = 1) SET @HasIdentity = 1;

	-- 4. Build CREATE TABLE Script
	SET @SQL = N'CREATE TABLE ' + @NewTable + ' (' + CHAR (10) ;

	DECLARE @ColsDef NVARCHAR (MAX) ;
	SELECT @ColsDef = STUFF ( (SELECT ',' + CHAR (10) + ' [' + ColName + '] ' + DataType +
					CASE WHEN IsIdentity = 1 THEN ' IDENTITY (1,1) ' ELSE '' END +
					CASE WHEN IsComputed = 1 THEN ' AS ' + ComputedDef + CASE WHEN IsPersisted = 1 THEN ' PERSISTED' ELSE ' ' END ELSE ' ' END +
					' ' + CASE WHEN IsNullable = 'YES' OR IsComputed = 1 THEN 'NULL' ELSE 'NOT NULL' END +
					-- Add '_New' to constraint name to avoid duplicate object name error
					ISNULL (' CONSTRAINT [DF_' + @TableName + '_New_' + ColName + '] DEFAULT ' + ColDefault, ' ') FROM @ColsTable
		ORDER BY SortOrder
		FOR XML PATH ('') , TYPE) .value ('.', 'NVARCHAR (MAX) ') , 1, 1, '') ;

	SET @SQL = @SQL + @ColsDef;

	-- Add PK
	IF EXISTS (SELECT 1 FROM sys.key_constraints WHERE type = 'PK' AND parent_object_id = OBJECT_ID (@OldTable)) BEGIN
		SELECT @PKCols = STUFF ( (SELECT ', [' + COL_NAME (ic.object_id, ic.column_id) + '] ' + CASE WHEN ic.is_descending_key = 1 THEN 'DESC' ELSE 'ASC' END
				FROM sys.key_constraints kc
				JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
				WHERE kc.type = 'PK' AND kc.parent_object_id = OBJECT_ID (@OldTable) ORDER BY ic.key_ordinal
				FOR XML PATH ('')) , 1, 2, '') ;

		SET @SQL = @SQL + CHAR (10) + ', CONSTRAINT [PK_' + @TableName + '_Temp] PRIMARY KEY CLUSTERED (' + @PKCols + ') ';
	END

	SET @SQL = @SQL + CHAR (10) + ') ;';

	EXEC sp_executesql @SQL;

	-- 5. Copy Data
	SELECT @ColList = STUFF ( (SELECT ', [' + ColName + ']'
		FROM @ColsTable
		WHERE IsComputed = 0
		ORDER BY SortOrder
		FOR XML PATH ('')) , 1, 2, '') ;

	SET @SQL = N'';
	IF @HasIdentity = 1
		SET @SQL = @SQL + N'SET IDENTITY_INSERT ' + @NewTable + ' ON; ' + CHAR (10) ;

	SET @SQL = @SQL + N'INSERT INTO ' + @NewTable + ' (' + @ColList + ') ' + CHAR (10) +
					 N'SELECT ' + @ColList + ' FROM ' + @OldTable + '; ' + CHAR (10) ;

	IF @HasIdentity = 1
		SET @SQL = @SQL + N'SET IDENTITY_INSERT ' + @NewTable + ' OFF;';

	EXEC sp_executesql @SQL;
	PRINT N'Data copy completed ' + CAST (@@ROWCOUNT AS NVARCHAR (20)) + N' rows';

	-- 6. Swap table names & FIX PK NAMES
	DECLARE @Timestamp NVARCHAR (20) ;
	DECLARE @BackupName NVARCHAR (128) ;

	SET @Timestamp = CONVERT (VARCHAR (8) , GETDATE () , 112) + '_' + REPLACE (CONVERT (VARCHAR (8) , GETDATE () , 108) , ':', '') ;
	SET @BackupName = @TableName + '_Old_' + @Timestamp;

	-- 6.1 Rename Old Table to Backup Name
	EXEC sp_rename @OldTable, @BackupName;

	-- 6.2 Rename the PK on the Backup Table to free up the original name
	DECLARE @CurrentOldPKName NVARCHAR (128) ;
	DECLARE @NewBackupPKName NVARCHAR (128) ;

	-- Find the name of the PK currently attached to the backup table
	SELECT @CurrentOldPKName = name
	FROM sys.key_constraints
	WHERE parent_object_id = OBJECT_ID (@SchemaName + '.' + @BackupName) AND type = 'PK';

	IF @CurrentOldPKName IS NOT NULL
	BEGIN
		-- Rename it: e.g. PK_MyTable -> PK_MyTable_Old_2024...
		SET @NewBackupPKName = @CurrentOldPKName + '_Old_' + @Timestamp;
		DECLARE @FullOldPKName NVARCHAR (258) = @SchemaName + '.' + @CurrentOldPKName;
		-- Check if length exceeds limit (optional safety, usually fine) IF LEN (@NewBackupPKName) > 128 SET @NewBackupPKName = LEFT (@NewBackupPKName, 128) ;
		EXEC sp_rename @FullOldPKName, @NewBackupPKName, 'OBJECT';
	END

	-- 6.3 Rename New Table to Original Name
	EXEC sp_rename @NewTable, @TableName;

	-- 6.4 Rename New PK Temp -> Original PK Name
	DECLARE @OldTempPKName NVARCHAR (128) ;
	DECLARE @RealPKName NVARCHAR (128) ;
	SET @OldTempPKName = @SchemaName + '.PK_' + @TableName + '_Temp';
	SET @RealPKName = 'PK_' + @TableName;

	IF EXISTS (SELECT 1 FROM sys.key_constraints WHERE name LIKE 'PK_' + @TableName + '_Temp') EXEC sp_rename @OldTempPKName, @RealPKName, 'OBJECT';

	-- 7. Recreate FKs
	IF LEN (@CreateFK) > 0 EXEC sp_executesql @CreateFK;

	COMMIT TRANSACTION;
	PRINT N'';
	PRINT N'SUCCESS! Columns reordered.';
	PRINT N'-------------------------------------------------------';

END TRY
BEGIN CATCH
	IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
	PRINT N'ERROR: ' + ERROR_MESSAGE () ;
	THROW;
END CATCH;