Indianrock (2/9/2016)

---

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