At this point I think Igor and Sergiy are tired and quoting me sometimes when they think they're quoting eachother. 🙂
RedGate's smart rename function came up with this for renaming the Line_number column to Line_Number_Archive
/*
Script created by SQL Prompt version 6.5.0.326 from Red Gate Software Ltd at 2/9/2016 6:13:39 AM
Run this script on cmsperformance to perform the Smart Rename refactoring.
Please back up your database before running this script.
*/
-- Summary for the smart rename:
--
-- Action:
-- Drop index [index_FILE_RECORD_Status_ID_Time] from [dbo].[FILE_RECORD]
-- Alter table [dbo].[FILE_RECORD]
-- Create index [index_FILE_RECORD_Status_ID_Time] on [dbo].[FILE_RECORD]
--
-- Warnings:
-- Medium: Objects need to be created using one or more non-standard filegroups. These filegroups may need to be created manually: CM_Index
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Dropping index [index_FILE_RECORD_Status_ID_Time] from [dbo].[FILE_RECORD]'
GO
DROP INDEX [index_FILE_RECORD_Status_ID_Time] ON [dbo].[FILE_RECORD]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Altering [dbo].[FILE_RECORD]'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
EXEC sp_rename N'[dbo].[FILE_RECORD].[LINE_NUMBER]', N'LINE_NUMBER_ARCHIVE', N'COLUMN'
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
PRINT N'Creating index [index_FILE_RECORD_Status_ID_Time] on [dbo].[FILE_RECORD]'
GO
CREATE NONCLUSTERED INDEX [index_FILE_RECORD_Status_ID_Time] ON [dbo].[FILE_RECORD] ([FILE_RECORD_STATUS], [FILE_DESCRIPTOR_ID], [MESSAGE_ID], [FILE_RECORD_ID], [CREATED_DATE_TIME]) INCLUDE ([ACCOUNT_DATA_ID], [CHECKSUM], [CONCRETE_TYPE], [FILE_ID], [LAST_MOD_DATE_TIME], [LAST_MODIFIER], [LINE_NUMBER_ARCHIVE], [LOGICAL_RECORD_DESCRIPTOR_REFERENCE_ID], [MESSAGE_DIRECTION], [MESSAGE_ERRORERROR_ID], [RAW_CONTENT], [RECORD_CONTEXT], [TRANS_SEQ_NUM]) WITH (FILLFACTOR=90) ON [CM_Index]
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
COMMIT TRANSACTION
GO
IF @@ERROR <> 0 SET NOEXEC ON
GO
DECLARE @Success AS BIT
SET @Success = 1
SET NOEXEC OFF
IF (@Success = 1) PRINT 'The database update succeeded'
ELSE BEGIN
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
PRINT 'The database update failed'
END
GO