Snippet Name: Rename_Indexes_with_If_Exists

Tags:

Created Date: 2025-06-16 13:12:48
Last Modified Date: 2025-05-31 10:31:36

Dependencies:

DBA Notes:

Sample Output:

Title: Rename Indexes with If Exists
Author: user
please modify these SQL server name commands to use an IF EXISTS() test to  check if the index name exists for the table: EXECUTE sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[PK_tblInstanceWorkflowsClosed]','~PK_tblInstanceWorkflowsClosed','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldEmployeeId_fldCompletionDate_fldId]','~IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldEmployeeId_fldCompletionDate_fldId','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldId_fldNextRedirectDate_fldStatus_fldLastUpdated]','~IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldId_fldNextRedirectDate_fldStatus_fldLastUpdated','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_tblInstanceWorkflowsCLosed_fldCompletionDate]','~IX_tblInstanceWorkflowsCLosed_fldCompletionDate','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_tblInstanceWorkflowsClosed_InstacesViewsByCreator]','~IX_tblInstanceWorkflowsClosed_InstacesViewsByCreator','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_tblInstanceWorkflowsClosed_InstacesViewsByEmployeeId]','~IX_tblInstanceWorkflowsClosed_InstacesViewsByEmployeeId','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_tblInstanceWorkflowsClosed_fldMasterIWfId]','~IX_tblInstanceWorkflowsClosed_fldMasterIWfId','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldCreationDate]','~IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldCreationDate','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_tblInstanceWorkflowsClosed_fldId_fldCompletionDate_fldStatus_fldEmployeeId]','~IX_tblInstanceWorkflowsClosed_fldId_fldCompletionDate_fldStatus_fldEmployeeId','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldCompletionDate_fldStatus_fldEmployeeId_fldId]','~IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldCompletionDate_fldStatus_fldEmployeeId_fldId','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_LI_tblInstanceWorkflowsClosed_fldSourceIWfId_fldId_Includes]','~IX_LI_tblInstanceWorkflowsClosed_fldSourceIWfId_fldId_Includes','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_LI_tblInstanceWorkflowsClosed_fldSourceIWfId_fldStatus_Includes]','~IX_LI_tblInstanceWorkflowsClosed_fldSourceIWfId_fldStatus_Includes','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_LI_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldCreatedByOriginalId_Includes]','~IX_LI_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldCreatedByOriginalId_Includes','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsRollbackClosed].[PK_tblInstanceWorkflowsRollbackClosed]','~PK_tblInstanceWorkflowsRollbackClosed','INDEX';
execute sp_rename '[dbamigration].[tblActionItemRemindersClosed].[PK_tblActionItemRemindersClosed]','~PK_tblActionItemRemindersClosed','INDEX';
execute sp_rename '[dbamigration].[tblActionItemRemindersClosed].[IX_tblActionItemRemindersClosed_fldUserId_fldUpdatedAt]','~IX_tblActionItemRemindersClosed_fldUserId_fldUpdatedAt','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsOrgsClosed].[PK_tblActionItemsOrgsClosed]','~PK_tblActionItemsOrgsClosed','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsOrgsClosed].[IX_tblActionItemsOrgsClosed_fldAIId]','~IX_tblActionItemsOrgsClosed_fldAIId','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsOrgsClosed].[IX_tblActionItemsOrgsClosed_fldOrgId]','~IX_tblActionItemsOrgsClosed_fldOrgId','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsOrgsClosed].[IX_tblActionItemsOrgsClosed_fldOrgId_fldOrgType]','~IX_tblActionItemsOrgsClosed_fldOrgId_fldOrgType','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsOrgsClosed].[IX_tblActionItemsOrgsClosed_fldOrgType]','~IX_tblActionItemsOrgsClosed_fldOrgType','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsOrgsClosed].[IX_LI_tblActionItemsOrgsClosed_fldAIId]','~IX_LI_tblActionItemsOrgsClosed_fldAIId','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesRollbackClosed].[PK_tblInstanceActivitiesRollbackClosed]','~PK_tblInstanceActivitiesRollbackClosed','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesRollbackClosed].[IX_tblInstanceActivitiesRollbackClosed_fldInstanceWfId]','~IX_tblInstanceActivitiesRollbackClosed_fldInstanceWfId','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsClosed].[PK_tblActionItemsClosed]','~PK_tblActionItemsClosed','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldIActId_fldId]','~IX_tblActionItemsClosed_fldIActId_fldId','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldToId_fldCompletionDate_fldMessageType_fldId_fldIWFId]','~IX_tblActionItemsClosed_fldToId_fldCompletionDate_fldMessageType_fldId_fldIWFId','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldToId_fldCreationDate_fldMessageMode_fldCompletionDate]','~IX_tblActionItemsClosed_fldToId_fldCreationDate_fldMessageMode_fldCompletionDate','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldIActId_fldIWfId_fldToId]','~IX_tblActionItemsClosed_fldIActId_fldIWfId_fldToId','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldToId_fldIActId]','~IX_tblActionItemsClosed_fldToId_fldIActId','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldIWfId_fldToId]','~IX_tblActionItemsClosed_fldIWfId_fldToId','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldToId_fldCompletionDate_fldCreationDate_fldMessageMode_fldMessageType_plus5WithInclude]','~IX_tblActionItemsClosed_fldToId_fldCompletionDate_fldCreationDate_fldMessageMode_fldMessageType_plus5WithInclude','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[PK_tblInstanceActivitiesClosed]','~PK_tblInstanceActivitiesClosed','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldEmployeeId_fldId_fldTemplateActivityGuid_fldInstanceWfId]','~IX_tblInstanceActivitiesClosed_fldEmployeeId_fldId_fldTemplateActivityGuid_fldInstanceWfId','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid]','~IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldId_fldTemplateActivityGuid_fldInstanceWfId_fldEmployeeCode]','~IX_tblInstanceActivitiesClosed_fldId_fldTemplateActivityGuid_fldInstanceWfId_fldEmployeeCode','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldInstanceWfId_fldId_fldEmployeeCode]','~IX_tblInstanceActivitiesClosed_fldInstanceWfId_fldId_fldEmployeeCode','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldRedirectFlag_fldStatus_fldInstanceWfId_fldTemplateActivityGuid]','~IX_tblInstanceActivitiesClosed_fldRedirectFlag_fldStatus_fldInstanceWfId_fldTemplateActivityGuid','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldStatus_fldInstanceWfId_fldTemplateActivityGuid_fldRedirectFlag]','~IX_tblInstanceActivitiesClosed_fldStatus_fldInstanceWfId_fldTemplateActivityGuid_fldRedirectFlag','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldSourceActId]','~IX_tblInstanceActivitiesClosed_fldSourceActId','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldCompletionDate_fldId_fldEmployeeCode_fldTemplateActivityGuid]','~IX_tblInstanceActivitiesClosed_fldCompletionDate_fldId_fldEmployeeCode_fldTemplateActivityGuid','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid_fldId]','~IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid_fldId','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid_Filtered]','~IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid_Filtered','INDEX';
execute sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_LI_tblInstanceActivitiesClosed_fldTemplateActivityGuid_fldCompletionDate_Includes]','~IX_LI_tblInstanceActivitiesClosed_fldTemplateActivityGuid_fldCompletionDate_Includes','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsDataPropagationClosed].[PK_tblInstanceWorkflowsDataPropagationClosed]','~PK_tblInstanceWorkflowsDataPropagationClosed','INDEX';
execute sp_rename '[dbamigration].[tblInstanceWorkflowsDataPropagationClosed].[IX_tblInstanceWorkflowsDataPropagationClosed_fldIWfId]','~IX_tblInstanceWorkflowsDataPropagationClosed_fldIWfId','INDEX';
execute sp_rename '[dbamigration].[tblActionItemsAttachmentsClosed].[PK_tblActionItemsAttachmentsClosed]','~PK_tblActionItemsAttachmentsClosed','INDEX';
execute sp_rename '[dbamigration].[tblAttachmentsClosed].[PK_tblAttachmentsClosed]','~PK_tblAttachmentsClosed','INDEX';
execute sp_rename '[dbamigration].[tblAttachmentsClosed].[IX_tblAttachmentsClosed_fldIActId]','~IX_tblAttachmentsClosed_fldIActId','INDEX';
execute sp_rename '[dbamigration].[tblAttachmentsClosed].[IX_tblAttachmentsClosed_fldAIId]','~IX_tblAttachmentsClosed_fldAIId','INDEX';

Author: ChatGPT
To modify these SQL Server commands to use an `IF EXISTS` test to check if the index name exists for the table before renaming, you can use the following approach. For each index, you'll need to query the `sys.indexes` view to determine if the index exists and then conditionally execute the `sp_rename` stored procedure. Here's how you can do this for each index:

```sql
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceWorkflowsClosed]') AND name = 'PK_tblInstanceWorkflowsClosed')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[PK_tblInstanceWorkflowsClosed]', '~PK_tblInstanceWorkflowsClosed', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceWorkflowsClosed]') AND name = 'IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldEmployeeId_fldCompletionDate_fldId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldEmployeeId_fldCompletionDate_fldId]', '~IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldEmployeeId_fldCompletionDate_fldId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceWorkflowsClosed]') AND name = 'IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldId_fldNextRedirectDate_fldStatus_fldLastUpdated')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldId_fldNextRedirectDate_fldStatus_fldLastUpdated]', '~IX_tblInstanceWorkflowsClosed_fldTemplateWfGuid_fldId_fldNextRedirectDate_fldStatus_fldLastUpdated', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceWorkflowsClosed]') AND name = 'IX_tblInstanceWorkflowsCLosed_fldCompletionDate')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceWorkflowsClosed].[IX_tblInstanceWorkflowsCLosed_fldCompletionDate]', '~IX_tblInstanceWorkflowsCLosed_fldCompletionDate', 'INDEX';
END

-- Repeat the pattern for each remaining index

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceWorkflowsRollbackClosed]') AND name = 'PK_tblInstanceWorkflowsRollbackClosed')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceWorkflowsRollbackClosed].[PK_tblInstanceWorkflowsRollbackClosed]', '~PK_tblInstanceWorkflowsRollbackClosed', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemRemindersClosed]') AND name = 'PK_tblActionItemRemindersClosed')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemRemindersClosed].[PK_tblActionItemRemindersClosed]', '~PK_tblActionItemRemindersClosed', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemRemindersClosed]') AND name = 'IX_tblActionItemRemindersClosed_fldUserId_fldUpdatedAt')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemRemindersClosed].[IX_tblActionItemRemindersClosed_fldUserId_fldUpdatedAt]', '~IX_tblActionItemRemindersClosed_fldUserId_fldUpdatedAt', 'INDEX';
END

-- Continue this pattern for all the remaining indexes
```

Repeat the above `IF EXISTS` block for each `sp_rename` command, adjusting the `OBJECT_ID` and `name` values accordingly. This ensures that each rename operation is only attempted if the specified index already exists on the table.

Author: user
continue

Author: ChatGPT
Certainly! I'll continue the pattern for the remaining indexes:

```sql
IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsOrgsClosed]') AND name = 'PK_tblActionItemsOrgsClosed')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsOrgsClosed].[PK_tblActionItemsOrgsClosed]', '~PK_tblActionItemsOrgsClosed', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsOrgsClosed]') AND name = 'IX_tblActionItemsOrgsClosed_fldAIId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsOrgsClosed].[IX_tblActionItemsOrgsClosed_fldAIId]', '~IX_tblActionItemsOrgsClosed_fldAIId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsOrgsClosed]') AND name = 'IX_tblActionItemsOrgsClosed_fldOrgId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsOrgsClosed].[IX_tblActionItemsOrgsClosed_fldOrgId]', '~IX_tblActionItemsOrgsClosed_fldOrgId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsOrgsClosed]') AND name = 'IX_tblActionItemsOrgsClosed_fldOrgId_fldOrgType')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsOrgsClosed].[IX_tblActionItemsOrgsClosed_fldOrgId_fldOrgType]', '~IX_tblActionItemsOrgsClosed_fldOrgId_fldOrgType', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsOrgsClosed]') AND name = 'IX_tblActionItemsOrgsClosed_fldOrgType')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsOrgsClosed].[IX_tblActionItemsOrgsClosed_fldOrgType]', '~IX_tblActionItemsOrgsClosed_fldOrgType', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsOrgsClosed]') AND name = 'IX_LI_tblActionItemsOrgsClosed_fldAIId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsOrgsClosed].[IX_LI_tblActionItemsOrgsClosed_fldAIId]', '~IX_LI_tblActionItemsOrgsClosed_fldAIId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesRollbackClosed]') AND name = 'PK_tblInstanceActivitiesRollbackClosed')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesRollbackClosed].[PK_tblInstanceActivitiesRollbackClosed]', '~PK_tblInstanceActivitiesRollbackClosed', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesRollbackClosed]') AND name = 'IX_tblInstanceActivitiesRollbackClosed_fldInstanceWfId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesRollbackClosed].[IX_tblInstanceActivitiesRollbackClosed_fldInstanceWfId]', '~IX_tblInstanceActivitiesRollbackClosed_fldInstanceWfId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsClosed]') AND name = 'PK_tblActionItemsClosed')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsClosed].[PK_tblActionItemsClosed]', '~PK_tblActionItemsClosed', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsClosed]') AND name = 'IX_tblActionItemsClosed_fldIActId_fldId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldIActId_fldId]', '~IX_tblActionItemsClosed_fldIActId_fldId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsClosed]') AND name = 'IX_tblActionItemsClosed_fldToId_fldCompletionDate_fldMessageType_fldId_fldIWFId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldToId_fldCompletionDate_fldMessageType_fldId_fldIWFId]', '~IX_tblActionItemsClosed_fldToId_fldCompletionDate_fldMessageType_fldId_fldIWFId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsClosed]') AND name = 'IX_tblActionItemsClosed_fldToId_fldCreationDate_fldMessageMode_fldCompletionDate')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldToId_fldCreationDate_fldMessageMode_fldCompletionDate]', '~IX_tblActionItemsClosed_fldToId_fldCreationDate_fldMessageMode_fldCompletionDate', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsClosed]') AND name = 'IX_tblActionItemsClosed_fldIActId_fldIWfId_fldToId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldIActId_fldIWfId_fldToId]', '~IX_tblActionItemsClosed_fldIActId_fldIWfId_fldToId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsClosed]') AND name = 'IX_tblActionItemsClosed_fldToId_fldIActId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldToId_fldIActId]', '~IX_tblActionItemsClosed_fldToId_fldIActId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsClosed]') AND name = 'IX_tblActionItemsClosed_fldIWfId_fldToId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldIWfId_fldToId]', '~IX_tblActionItemsClosed_fldIWfId_fldToId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsClosed]') AND name = 'IX_tblActionItemsClosed_fldToId_fldCompletionDate_fldCreationDate_fldMessageMode_fldMessageType_plus5WithInclude')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsClosed].[IX_tblActionItemsClosed_fldToId_fldCompletionDate_fldCreationDate_fldMessageMode_fldMessageType_plus5WithInclude]', '~IX_tblActionItemsClosed_fldToId_fldCompletionDate_fldCreationDate_fldMessageMode_fldMessageType_plus5WithInclude', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesClosed]') AND name = 'PK_tblInstanceActivitiesClosed')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[PK_tblInstanceActivitiesClosed]', '~PK_tblInstanceActivitiesClosed', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesClosed]') AND name = 'IX_tblInstanceActivitiesClosed_fldEmployeeId_fldId_fldTemplateActivityGuid_fldInstanceWfId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldEmployeeId_fldId_fldTemplateActivityGuid_fldInstanceWfId]', '~IX_tblInstanceActivitiesClosed_fldEmployeeId_fldId_fldTemplateActivityGuid_fldInstanceWfId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesClosed]') AND name = 'IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid]', '~IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesClosed]') AND name = 'IX_tblInstanceActivitiesClosed_fldId_fldTemplateActivityGuid_fldInstanceWfId_fldEmployeeCode')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldId_fldTemplateActivityGuid_fldInstanceWfId_fldEmployeeCode]', '~IX_tblInstanceActivitiesClosed_fldId_fldTemplateActivityGuid_fldInstanceWfId_fldEmployeeCode', 'INDEX';
END

IF EXISTS (SELECT

Author: ChatGPT
 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesClosed]') AND name = 'IX_tblInstanceActivitiesClosed_fldInstanceWfId_fldId_fldEmployeeCode')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldInstanceWfId_fldId_fldEmployeeCode]', '~IX_tblInstanceActivitiesClosed_fldInstanceWfId_fldId_fldEmployeeCode', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesClosed]') AND name = 'IX_tblInstanceActivitiesClosed_fldRedirectFlag_fldStatus_fldInstanceWfId_fldTemplateActivityGuid')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldRedirectFlag_fldStatus_fldInstanceWfId_fldTemplateActivityGuid]', '~IX_tblInstanceActivitiesClosed_fldRedirectFlag_fldStatus_fldInstanceWfId_fldTemplateActivityGuid', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesClosed]') AND name = 'IX_tblInstanceActivitiesClosed_fldStatus_fldInstanceWfId_fldTemplateActivityGuid_fldRedirectFlag')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldStatus_fldInstanceWfId_fldTemplateActivityGuid_fldRedirectFlag]', '~IX_tblInstanceActivitiesClosed_fldStatus_fldInstanceWfId_fldTemplateActivityGuid_fldRedirectFlag', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesClosed]') AND name = 'IX_tblInstanceActivitiesClosed_fldSourceActId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldSourceActId]', '~IX_tblInstanceActivitiesClosed_fldSourceActId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesClosed]') AND name = 'IX_tblInstanceActivitiesClosed_fldCompletionDate_fldId_fldEmployeeCode_fldTemplateActivityGuid')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldCompletionDate_fldId_fldEmployeeCode_fldTemplateActivityGuid]', '~IX_tblInstanceActivitiesClosed_fldCompletionDate_fldId_fldEmployeeCode_fldTemplateActivityGuid', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesClosed]') AND name = 'IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid_fldId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid_fldId]', '~IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid_fldId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesClosed]') AND name = 'IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid_Filtered')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid_Filtered]', '~IX_tblInstanceActivitiesClosed_fldTemplateActivityGuid_Filtered', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceActivitiesClosed]') AND name = 'IX_LI_tblInstanceActivitiesClosed_fldTemplateActivityGuid_fldCompletionDate_Includes')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceActivitiesClosed].[IX_LI_tblInstanceActivitiesClosed_fldTemplateActivityGuid_fldCompletionDate_Includes]', '~IX_LI_tblInstanceActivitiesClosed_fldTemplateActivityGuid_fldCompletionDate_Includes', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceWorkflowsDataPropagationClosed]') AND name = 'PK_tblInstanceWorkflowsDataPropagationClosed')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceWorkflowsDataPropagationClosed].[PK_tblInstanceWorkflowsDataPropagationClosed]', '~PK_tblInstanceWorkflowsDataPropagationClosed', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblInstanceWorkflowsDataPropagationClosed]') AND name = 'IX_tblInstanceWorkflowsDataPropagationClosed_fldIWfId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblInstanceWorkflowsDataPropagationClosed].[IX_tblInstanceWorkflowsDataPropagationClosed_fldIWfId]', '~IX_tblInstanceWorkflowsDataPropagationClosed_fldIWfId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblActionItemsAttachmentsClosed]') AND name = 'PK_tblActionItemsAttachmentsClosed')
BEGIN
    EXEC sp_rename '[dbamigration].[tblActionItemsAttachmentsClosed].[PK_tblActionItemsAttachmentsClosed]', '~PK_tblActionItemsAttachmentsClosed', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblAttachmentsClosed]') AND name = 'PK_tblAttachmentsClosed')
BEGIN
    EXEC sp_rename '[dbamigration].[tblAttachmentsClosed].[PK_tblAttachmentsClosed]', '~PK_tblAttachmentsClosed', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblAttachmentsClosed]') AND name = 'IX_tblAttachmentsClosed_fldIActId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblAttachmentsClosed].[IX_tblAttachmentsClosed_fldIActId]', '~IX_tblAttachmentsClosed_fldIActId', 'INDEX';
END

IF EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('[dbamigration].[tblAttachmentsClosed]') AND name = 'IX_tblAttachmentsClosed_fldAIId')
BEGIN
    EXEC sp_rename '[dbamigration].[tblAttachmentsClosed].[IX_tblAttachmentsClosed_fldAIId]', '~IX_tblAttachmentsClosed_fldAIId', 'INDEX';
END
```

This script checks for the existence of each index before attempting to rename it, ensuring that you only attempt to rename indexes that already exist on the respective tables. This approach helps avoid errors that would occur if the index does not exist.

Author: user
for SQL Server 2014 ISSIS pacakges, can i deploy teh projects to SQL2019's integration services catalog with no changes, or do they have to be upgraded?

Author: ChatGPT
When dealing with SQL Server Integration Services (SSIS) packages created in an older version of SQL Server (such as SQL Server 2014) and deploying them to a newer version (such as SQL Server 2019), there are a few key considerations:

1. **Compatibility and Upgrade**: 
   - SSIS packages created in SQL Server 2014 should generally be compatible with SQL Server 2019. However, when you deploy these packages to SQL Server 2019, the packages will undergo an automatic upgrade process. This means that while you can deploy and run them, the newer SQL Server version will convert the packages to its internal format.
   
2. **Testing**:
   - It's crucial to thoroughly test the packages after deployment to ensure that they work as expected. While the automatic upgrade process is designed to preserve functionality, there might be subtle changes or deprecated features that could impact package behavior.
   
3. **Backward Compatibility**:
   - Once packages are upgraded to the SQL Server 2019 format, they cannot be reverted back to SQL Server 2014 format. So, maintain a backup of the original SSIS packages if you need to run them on SQL Server 2014 in the future.

4. **Deployment Steps**:
   - Deploying SSIS packages from SQL Server 2014 to SQL Server 2019 involves these steps:
     1. **Build the SSIS Project**: Build your SSIS project in SQL Server Data Tools (SSDT) or Visual Studio.
     2. **Create an SSIS Catalog in SQL Server 2019**: Ensure the SSISDB catalog is created on your SQL Server 2019 instance.
     3. **Deploy to SSISDB**: Use the Integration Services Deployment Wizard to deploy the packages to the SSISDB catalog in SQL Server 2019.

5. **Using the Deployment Wizard**:
   - When using the SSIS Deployment Wizard, you can select the SSIS project deployment file (*.ispac) created in SSDT and follow the wizard steps to deploy the project to the SSISDB catalog on the SQL Server 2019 instance.

6. **Consider Upgrading SSIS Projects**:
   - If you have access to SSDT for SQL Server 2019, it is recommended to open and upgrade your SSIS projects in this environment. This process ensures that you can address any compatibility issues upfront.

### Steps for Deploying SSIS Packages from SQL Server 2014 to SQL Server 2019:

1. **Open the Solution**:
   - Open your SSIS solution in SSDT or Visual Studio compatible with SQL Server 2014.

2. **Build the Project**:
   - Build the SSIS project to generate the .ispac file.

3. **Deploy the .ispac File**:
   - Open SQL Server Management Studio (SSMS) connected to your SQL Server 2019 instance.
   - Right-click on the SSISDB catalog and choose ???Deploy Project???.
   - Follow the Deployment Wizard, selecting the .ispac file you built earlier.

4. **Validation and Testing**:
   - After deployment, validate the deployed packages in the SSISDB catalog.
   - Run the packages to ensure they execute without issues.

### Summary

While SSIS packages created in SQL Server 2014 can be deployed to SQL Server 2019 without manual changes, they will be automatically upgraded. It is critical to thoroughly test the packages post-deployment to ensure compatibility and functionality. Keeping backups of your original packages before deployment is also a good practice.

Author: user
can you write a powershell script that would migrate all projects from sql2014 server to a sql2019 server, includng maintaining their folder information? the pla is from AZU-V1-DEV1.cigfinancial.com,1433 to vmprodwdbssis01.cigfinancial.com,1433

Author: ChatGPT
Certainly! Below is a PowerShell script that uses SQL Server Management Objects (SMO) and Integration Services Management Object Model (IS Management Objects) to migrate all SSIS projects from a SQL Server 2014 instance to a SQL Server 2019 instance, maintaining the folder structure.

First, you need to ensure you have the necessary modules and permissions to execute this script. Make sure the SQL Server Management Objects (SMO) and IS Management Objects are installed.

Here is the PowerShell script:

```powershell
# Load the necessary assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices") | Out-Null

# Source and destination server information
$sourceServer = "AZU-V1-DEV1.cigfinancial.com,1433"
$destinationServer = "vmprodwdbssis01.cigfinancial.com,1433"

# Function to migrate SSIS projects
function Migrate-SSISProjects {
    param (
        [string]$sourceServer,
        [string]$destinationServer
    )

    # Connect to source and destination Integration Services
    $sourceConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($sourceServer)
    $sourceISServer = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices($sourceConnection)
    $sourceISCatalog = $sourceISServer.Catalogs["SSISDB"]

    $destinationConnection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($destinationServer)
    $destinationISServer = New-Object Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices($destinationConnection)
    $destinationISCatalog = $destinationISServer.Catalogs["SSISDB"]

    # Iterate through each folder in the source SSISDB catalog
    foreach ($folder in $sourceISCatalog.Folders) {
        Write-Output "Migrating folder: $($folder.Name)"

        # Create the folder in the destination if it doesn't exist
        if (-not $destinationISCatalog.Folders[$folder.Name]) {
            $destinationISCatalog.Folders.Add($folder.Name)
            $destinationISCatalog.Folders[$folder.Name].Create()
        }

        $destinationFolder = $destinationISCatalog.Folders[$folder.Name]

        # Iterate through each project in the folder
        foreach ($project in $folder.Projects) {
            Write-Output "Migrating project: $($project.Name)"
            
            # Export the project from the source
            $projectFile = "$($env:TEMP)\$($project.Name).ispac"
            $project.ExportProject($projectFile)

            # Import the project to the destination
            $destinationFolder.Projects.Add($project.Name, [System.IO.File]::ReadAllBytes($projectFile))
            $destinationFolder.Projects[$project.Name].Create()
            
            # Clean up the exported file
            Remove-Item $projectFile
        }
    }

    Write-Output "Migration complete."
}

# Execute the migration
Migrate-SSISProjects -sourceServer $sourceServer -destinationServer $destinationServer
```

### Explanation:

1. **Loading Assemblies**: The script loads the necessary SQL Server Management Objects and Integration Services Management Objects assemblies.
2. **Server Connections**: It sets up the connection to both the source (SQL Server 2014) and destination (SQL Server 2019) servers.
3. **Migration Function**: The `Migrate-SSISProjects` function handles the migration process.
   - It iterates through each folder in the SSISDB catalog on the source server.
   - It creates the same folder on the destination server if it does not exist.
   - For each project in the folder, it exports the project from the source server to a temporary file and then imports that project into the corresponding folder on the destination server.
   - Temporary files are cleaned up after the import.

### Prerequisites:
- Ensure that the `SSISDB` catalog is already created on the destination server.
- The script requires appropriate permissions to read from the source SSISDB and write to the destination SSISDB.
- PowerShell execution policy should allow the script to run.

### Usage:
Run this script from a machine that has network access to both the source and destination SQL Servers. You may need to adjust the script for specific environmental setups, such as authentication methods if different from Windows Authentication.

Author: user
i've plugged in a USB drive in windows, but it is not being recognized by teh operating system. what tools can i use to diagnose teh drive,a dn maybe fix it

Author: ChatGPT
When a USB drive is not recognized by Windows, it can be due to various reasons such as driver issues, file system errors, or physical problems with the drive itself. Here are some steps and tools you can use to diagnose and potentially fix the issue:

### Step-by-Step Diagnostic and Repair Process

1. **Check Physical Connection and USB Port**:
   - Ensure the USB drive is properly inserted into the port.
   - Try connecting the USB drive to a different USB port on your computer.
   - Connect the USB drive to another computer to see if it is recognized.

2. **Device Manager**:
   - Press `Win + X` and select `Device Manager`.
   - Look under `Disk drives` and `Universal Serial Bus controllers` for any devices with a yellow exclamation mark or unknown devices.
   - If you find your USB drive listed with a warning, right-click it, select `Update driver`, and follow the prompts.

3. **Disk Management**:
   - Press `Win + X` and select `Disk Management`.
   - Check if the USB drive appears in the list of disks. It might be listed without a drive letter or as `Unallocated`.
   - If it appears without a drive letter, right-click it and select `Change Drive Letter and Paths`, then add a new drive letter.
   - If it appears as `Unallocated`, you may need to create a new volume. Right-click the unallocated space, select `New Simple Volume`, and follow the wizard.

4. **CHKDSK Utility**:
   - Open Command Prompt as an administrator.
   - Run `chkdsk X: /f` (replace `X` with the drive letter of your USB drive) to check and fix file system errors.

5. **Driver Update**:
   - In Device Manager, right-click the USB drive and select `Update driver`.
   - You can also visit the USB drive manufacturer???s website to download and install the latest drivers.

6. **USB Device Troubleshooter**:
   - Windows includes a built-in troubleshooter for USB devices.
   - Go to `Settings > Update & Security > Troubleshoot > Additional troubleshooters`.
   - Find and run the `Hardware and Devices` troubleshooter.

### Tools for Diagnosing and Repairing USB Drives

1. **MiniTool Partition Wizard**:
   - A powerful disk management tool that can help you diagnose and fix issues with your USB drive.
   - You can download it from [MiniTool's official website](https://www.partitionwizard.com/).
   - It includes features for checking file system integrity, formatting, and partitioning drives.

2. **CrystalDiskInfo**:
   - A utility that provides detailed information about your USB drive???s health and SMART status.
   - Download from [CrystalDiskInfo's official website](https://crystalmark.info/en/software/crystaldiskinfo/).

3. **HP USB Disk Storage Format Tool**:
   - Useful for formatting USB drives that are not recognized or have corrupted file systems.
   - Download from various software repositories like Softpedia.

4. **HDD Low Level Format Tool**:
   - A low-level formatting tool that can help revive unresponsive USB drives by completely erasing and reinitializing them.
   - Download from [HDDGURU's official website](http://hddguru.com/software/HDD-LLF-Low-Level-Format-Tool/).

5. **TestDisk**:
   - A powerful open-source data recovery tool that can fix partition tables, recover deleted partitions, and more.
   - Download from [CGSecurity's official website](https://www.cgsecurity.org/wiki/TestDisk).

### Additional Tips

- **Backup Important Data**: If the drive is recognized but data is inaccessible, try to back up any critical data before attempting repairs.
- **Check for Physical Damage**: Inspect the USB drive and port for any physical damage. Sometimes, physical damage can cause recognition issues.
- **USB Power Issues**: Ensure your USB port is supplying enough power, especially for larger drives. Try using a powered USB hub.

By following these steps and using the recommended tools, you should be able to diagnose and potentially fix issues with your USB drive. If the drive still isn???t recognized, it might be due to a hardware failure, and you may need to consult a professional data recovery service.

Author: user
please refactor these indexes with IF NOT EXISTS logic: create NONCLUSTERED index AccountName on PaymentGatewayProfile (AccountName); 
create NONCLUSTERED index CIG_acctrefno_transaction_date_Includes on loanacct_trans_history (transaction_code, reversal_transrefno, transaction_amount, acctrefno, transaction_date); 
create NONCLUSTERED index CIG_acctrefno_year_number_IDX on loanacct_statistics (acctrefno, year_number); 
create NONCLUSTERED index CIG_campaign_no_Includes on campaign_list_detail (acctrefno, campaign_no); 
create NONCLUSTERED index CIG_comment_description_Includes on loanacct_comments (acctrefno, created, comments, comment_description); 
create NONCLUSTERED index CIG_created_by_collection_code_no_created_Includes on loanacct_collections (acctrefno, created_by, collection_code_no, created); 
create NONCLUSTERED index CIG_DealerRpt_task_detail_IDX on task_detail (userdef82, userdef88); 
create NONCLUSTERED index CIG_IX_cif_detail_userdef06 on cif_detail (userdef06); 
create NONCLUSTERED index CIG_IX_cif_portfolio_code_id_company on cif (cifnumber, state, portfolio_code_id, company); 
create NONCLUSTERED index CIG_IX_collateral_vehicle_title_type_title_status on collateral_vehicle (collateral_id, title_type, title_status); 
create NONCLUSTERED index CIG_IX_loanacct_collateral_link_collateral_id on loanacct_collateral_link (collateral_id); 
create NONCLUSTERED index CIG_IX_loanacct_collections_result_code_id on loanacct_collections (result_code_no); 
create NONCLUSTERED index CIG_IX_loanacct_current_principal_balance on loanacct (loan_group_no, dealer_cifno, shortname, loan_number, status_code_no, loan_officer_no, current_principal_balance); 
create NONCLUSTERED index CIG_IX_loanacct_mod_history_item_changed_mod_datestamp on loanacct_mod_history (acctrefno, old_value, item_changed, mod_datestamp); 
create NONCLUSTERED index CIG_IX_loanacct_payment_history_payment_method_no_date_paid_gl_date on loanacct_payment_history (acctrefno, payment_method_no, date_paid, gl_date); 
create NONCLUSTERED index CIG_IX_loanacct_statuses_status_code_no on loanacct_statuses (acctrefno, effective_date, status_code_no); 
create NONCLUSTERED index CIG_IX_loanacct_trans_history_transaction_code_transaction_date on loanacct_trans_history (acctrefno, voucher_id, transaction_amount, user_reference, transaction_code, transaction_date); 
create NONCLUSTERED index CIG_IX_task_detail_userdef16_userdef87 on task_detail (userdef16, userdef87); 
create NONCLUSTERED index CIG_IX_task_modification_history_IDX on task_modification_history (task_refno, item_changed, new_value, mod_datestamp); 
create NONCLUSTERED index CIG_IX_task_modification_history_item_changed_new_value_mod_datestamp on task_modification_history (task_refno, item_changed, new_value, mod_datestamp); 
create NONCLUSTERED index CIG_IX_task_refno_IDX on task (NLS_refno); 
create NONCLUSTERED index CIG_IX_task_task_template_no_owner_uid on task (NLS_refno, task_template_no, owner_uid); 
create NONCLUSTERED index CIG_loanacct_cifno_IDX on loanacct (cifno); 
create NONCLUSTERED index CIG_loanacct_collections_acctrefno on loanacct_collections (created, comments, acctrefno, collection_code_no); 
create NONCLUSTERED index CIG_loanacct_collections_ptp_IDX on loanacct_collections_ptp (loanacct_collections_row_id, promise_kept_date, promise_broken_date, promise_amount); 
create NONCLUSTERED index CIG_loanacct_mod_history_item_Changed_IDX on loanacct_mod_history (old_value, new_value, acctrefno, item_changed); 
create NONCLUSTERED index CIG_loanacct_payment_history_payment_type_IDX on loanacct_payment_history (acctrefno, nsf_flag, payment_type, gl_date, payment_method_no, nsf_date); 
create NONCLUSTERED index CIG_loanacct_payments_due_acctrefno_IDX on loanacct_payments_due (date_due, acctrefno); 
create NONCLUSTERED index CIG_loanacct_statuses_IDX on loanacct_statuses (acctrefno, status_code_no); 
create NONCLUSTERED index CIG_loanacct_statuses_status_code_no_IDX on loanacct_statuses (status_code_no); 
create NONCLUSTERED index CIG_loanacct_trans_history_payment_method_IDX on loanacct_trans_history (gl_date, acctrefno, reversal_transrefno, payment_method_no); 
create NONCLUSTERED index CIG_nlsusers_IDX on nlsusers (userno, title, department, disabled); 
create NONCLUSTERED index CIG_task_template_no_IDX on task (task_template_no, completion_date, status_code_id); 
create NONCLUSTERED index CIG_TEMP on loanacct (cifno, status_code_no, loan_group_no, current_principal_balance); 
create CLUSTERED index document_template_key on document_templates (document_template_id); 
create CLUSTERED index EmailServerID on EmailServer (EmailServerID); 
create NONCLUSTERED index EmailServerName on EmailServer (EmailServerName); 
create CLUSTERED index GlobalVerificationDocsLinksPK on GlobalVerificationDocsLink (VerificationDocsID, global_verification_id); 
create CLUSTERED index GlobalVerificationDocsPK on GlobalVerificationDocs (VerificationDocsID); 
create NONCLUSTERED index IX_loanacct_coll_status on loanacct_coll_status (next_contact_date); 
create CLUSTERED index XAK1addressbook_cifno on cif_addressbook (cifno); 
create CLUSTERED index XI165322pending_transactions on pending_transactions (row_id); 
create CLUSTERED index XIE1cif_comments on cif_comments (cifno); 
create CLUSTERED index XIE1cif_modification_history on cif_modification_history (cifno); 
create CLUSTERED index zip_code_index on ZIPData (ZipCode);


Author: ChatGPT
To refactor the provided index creation statements to include `IF NOT EXISTS` checks, we can use the `sys.indexes` system catalog view to check if an index already exists before attempting to create it. Here is the refactored SQL code:

```sql
-- Indexes on PaymentGatewayProfile
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('PaymentGatewayProfile') AND name = 'AccountName')
    CREATE NONCLUSTERED INDEX AccountName ON PaymentGatewayProfile (AccountName);

-- Indexes on loanacct_trans_history
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_trans_history') AND name = 'CIG_acctrefno_transaction_date_Includes')
    CREATE NONCLUSTERED INDEX CIG_acctrefno_transaction_date_Includes ON loanacct_trans_history (transaction_code, reversal_transrefno, transaction_amount, acctrefno, transaction_date);

-- Indexes on loanacct_statistics
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_statistics') AND name = 'CIG_acctrefno_year_number_IDX')
    CREATE NONCLUSTERED INDEX CIG_acctrefno_year_number_IDX ON loanacct_statistics (acctrefno, year_number);

-- Indexes on campaign_list_detail
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('campaign_list_detail') AND name = 'CIG_campaign_no_Includes')
    CREATE NONCLUSTERED INDEX CIG_campaign_no_Includes ON campaign_list_detail (acctrefno, campaign_no);

-- Indexes on loanacct_comments
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_comments') AND name = 'CIG_comment_description_Includes')
    CREATE NONCLUSTERED INDEX CIG_comment_description_Includes ON loanacct_comments (acctrefno, created, comments, comment_description);

-- Indexes on loanacct_collections
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_collections') AND name = 'CIG_created_by_collection_code_no_created_Includes')
    CREATE NONCLUSTERED INDEX CIG_created_by_collection_code_no_created_Includes ON loanacct_collections (acctrefno, created_by, collection_code_no, created);

-- Indexes on task_detail
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('task_detail') AND name = 'CIG_DealerRpt_task_detail_IDX')
    CREATE NONCLUSTERED INDEX CIG_DealerRpt_task_detail_IDX ON task_detail (userdef82, userdef88);

-- Indexes on cif_detail
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('cif_detail') AND name = 'CIG_IX_cif_detail_userdef06')
    CREATE NONCLUSTERED INDEX CIG_IX_cif_detail_userdef06 ON cif_detail (userdef06);

-- Indexes on cif
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('cif') AND name = 'CIG_IX_cif_portfolio_code_id_company')
    CREATE NONCLUSTERED INDEX CIG_IX_cif_portfolio_code_id_company ON cif (cifnumber, state, portfolio_code_id, company);

-- Indexes on collateral_vehicle
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('collateral_vehicle') AND name = 'CIG_IX_collateral_vehicle_title_type_title_status')
    CREATE NONCLUSTERED INDEX CIG_IX_collateral_vehicle_title_type_title_status ON collateral_vehicle (collateral_id, title_type, title_status);

-- Indexes on loanacct_collateral_link
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_collateral_link') AND name = 'CIG_IX_loanacct_collateral_link_collateral_id')
    CREATE NONCLUSTERED INDEX CIG_IX_loanacct_collateral_link_collateral_id ON loanacct_collateral_link (collateral_id);

-- Indexes on loanacct_collections
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_collections') AND name = 'CIG_IX_loanacct_collections_result_code_id')
    CREATE NONCLUSTERED INDEX CIG_IX_loanacct_collections_result_code_id ON loanacct_collections (result_code_no);

-- Indexes on loanacct
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct') AND name = 'CIG_IX_loanacct_current_principal_balance')
    CREATE NONCLUSTERED INDEX CIG_IX_loanacct_current_principal_balance ON loanacct (loan_group_no, dealer_cifno, shortname, loan_number, status_code_no, loan_officer_no, current_principal_balance);

-- Indexes on loanacct_mod_history
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_mod_history') AND name = 'CIG_IX_loanacct_mod_history_item_changed_mod_datestamp')
    CREATE NONCLUSTERED INDEX CIG_IX_loanacct_mod_history_item_changed_mod_datestamp ON loanacct_mod_history (acctrefno, old_value, item_changed, mod_datestamp);

-- Indexes on loanacct_payment_history
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_payment_history') AND name = 'CIG_IX_loanacct_payment_history_payment_method_no_date_paid_gl_date')
    CREATE NONCLUSTERED INDEX CIG_IX_loanacct_payment_history_payment_method_no_date_paid_gl_date ON loanacct_payment_history (acctrefno, payment_method_no, date_paid, gl_date);

-- Indexes on loanacct_statuses
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_statuses') AND name = 'CIG_IX_loanacct_statuses_status_code_no')
    CREATE NONCLUSTERED INDEX CIG_IX_loanacct_statuses_status_code_no ON loanacct_statuses (acctrefno, effective_date, status_code_no);

-- Indexes on loanacct_trans_history
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_trans_history') AND name = 'CIG_IX_loanacct_trans_history_transaction_code_transaction_date')
    CREATE NONCLUSTERED INDEX CIG_IX_loanacct_trans_history_transaction_code_transaction_date ON loanacct_trans_history (acctrefno, voucher_id, transaction_amount, user_reference, transaction_code, transaction_date);

-- Indexes on task_detail
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('task_detail') AND name = 'CIG_IX_task_detail_userdef16_userdef87')
    CREATE NONCLUSTERED INDEX CIG_IX_task_detail_userdef16_userdef87 ON task_detail (userdef16, userdef87);

-- Indexes on task_modification_history
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('task_modification_history') AND name = 'CIG_IX_task_modification_history_IDX')
    CREATE NONCLUSTERED INDEX CIG_IX_task_modification_history_IDX ON task_modification_history (task_refno, item_changed, new_value, mod_datestamp);

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('task_modification_history') AND name = 'CIG_IX_task_modification_history_item_changed_new_value_mod_datestamp')
    CREATE NONCLUSTERED INDEX CIG_IX_task_modification_history_item_changed_new_value_mod_datestamp ON task_modification_history (task_refno, item_changed, new_value, mod_datestamp);

-- Indexes on task
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('task') AND name = 'CIG_IX_task_refno_IDX')
    CREATE NONCLUSTERED INDEX CIG_IX_task_refno_IDX ON task (NLS_refno);

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('task') AND name = 'CIG_IX_task_task_template_no_owner_uid')
    CREATE NONCLUSTERED INDEX CIG_IX_task_task_template_no_owner_uid ON task (NLS_refno, task_template_no, owner_uid);

-- Indexes on loanacct
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct') AND name = 'CIG_loanacct_cifno_IDX')
    CREATE NONCLUSTERED INDEX CIG_loanacct_cifno_IDX ON loanacct (cifno);

-- Indexes on loanacct_collections
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_collections') AND name = 'CIG_loanacct_collections_acctrefno')
    CREATE NONCLUSTERED INDEX CIG_loanacct_collections_acctrefno ON loanacct_collections (created, comments, acctrefno, collection_code_no);

-- Indexes on loanacct_collections_ptp
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_collections_ptp') AND name = 'CIG_loanacct_collections_ptp_IDX')
    CREATE NONCLUSTERED INDEX CIG_loanacct_collections_ptp_IDX ON loanacct_collections_ptp (loanacct_collections_row_id, promise_kept_date, promise_broken_date, promise_amount);

-- Indexes on loanacct_mod_history
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE

Author: ChatGPT
 object_id = OBJECT_ID('loanacct_mod_history') AND name = 'CIG_loanacct_mod_history_item_Changed_IDX')
    CREATE NONCLUSTERED INDEX CIG_loanacct_mod_history_item_Changed_IDX ON loanacct_mod_history (old_value, new_value, acctrefno, item_changed);

-- Indexes on loanacct_payment_history
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_payment_history') AND name = 'CIG_loanacct_payment_history_payment_type_IDX')
    CREATE NONCLUSTERED INDEX CIG_loanacct_payment_history_payment_type_IDX ON loanacct_payment_history (acctrefno, nsf_flag, payment_type, gl_date, payment_method_no, nsf_date);

-- Indexes on loanacct_payments_due
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_payments_due') AND name = 'CIG_loanacct_payments_due_acctrefno_IDX')
    CREATE NONCLUSTERED INDEX CIG_loanacct_payments_due_acctrefno_IDX ON loanacct_payments_due (date_due, acctrefno);

-- Indexes on loanacct_statuses
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_statuses') AND name = 'CIG_loanacct_statuses_IDX')
    CREATE NONCLUSTERED INDEX CIG_loanacct_statuses_IDX ON loanacct_statuses (acctrefno, status_code_no);

IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_statuses') AND name = 'CIG_loanacct_statuses_status_code_no_IDX')
    CREATE NONCLUSTERED INDEX CIG_loanacct_statuses_status_code_no_IDX ON loanacct_statuses (status_code_no);

-- Indexes on loanacct_trans_history
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_trans_history') AND name = 'CIG_loanacct_trans_history_payment_method_IDX')
    CREATE NONCLUSTERED INDEX CIG_loanacct_trans_history_payment_method_IDX ON loanacct_trans_history (gl_date, acctrefno, reversal_transrefno, payment_method_no);

-- Indexes on nlsusers
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('nlsusers') AND name = 'CIG_nlsusers_IDX')
    CREATE NONCLUSTERED INDEX CIG_nlsusers_IDX ON nlsusers (userno, title, department, disabled);

-- Indexes on task
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('task') AND name = 'CIG_task_template_no_IDX')
    CREATE NONCLUSTERED INDEX CIG_task_template_no_IDX ON task (task_template_no, completion_date, status_code_id);

-- Indexes on loanacct
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct') AND name = 'CIG_TEMP')
    CREATE NONCLUSTERED INDEX CIG_TEMP ON loanacct (cifno, status_code_no, loan_group_no, current_principal_balance);

-- Clustered index on document_templates
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('document_templates') AND name = 'document_template_key')
    CREATE CLUSTERED INDEX document_template_key ON document_templates (document_template_id);

-- Clustered index on EmailServer
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('EmailServer') AND name = 'EmailServerID')
    CREATE CLUSTERED INDEX EmailServerID ON EmailServer (EmailServerID);

-- Indexes on EmailServer
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('EmailServer') AND name = 'EmailServerName')
    CREATE NONCLUSTERED INDEX EmailServerName ON EmailServer (EmailServerName);

-- Clustered index on GlobalVerificationDocsLink
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('GlobalVerificationDocsLink') AND name = 'GlobalVerificationDocsLinksPK')
    CREATE CLUSTERED INDEX GlobalVerificationDocsLinksPK ON GlobalVerificationDocsLink (VerificationDocsID, global_verification_id);

-- Clustered index on GlobalVerificationDocs
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('GlobalVerificationDocs') AND name = 'GlobalVerificationDocsPK')
    CREATE CLUSTERED INDEX GlobalVerificationDocsPK ON GlobalVerificationDocs (VerificationDocsID);

-- Indexes on loanacct_coll_status
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('loanacct_coll_status') AND name = 'IX_loanacct_coll_status')
    CREATE NONCLUSTERED INDEX IX_loanacct_coll_status ON loanacct_coll_status (next_contact_date);

-- Clustered index on cif_addressbook
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('cif_addressbook') AND name = 'XAK1addressbook_cifno')
    CREATE CLUSTERED INDEX XAK1addressbook_cifno ON cif_addressbook (cifno);

-- Clustered index on pending_transactions
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('pending_transactions') AND name = 'XI165322pending_transactions')
    CREATE CLUSTERED INDEX XI165322pending_transactions ON pending_transactions (row_id);

-- Clustered index on cif_comments
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('cif_comments') AND name = 'XIE1cif_comments')
    CREATE CLUSTERED INDEX XIE1cif_comments ON cif_comments (cifno);

-- Clustered index on cif_modification_history
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('cif_modification_history') AND name = 'XIE1cif_modification_history')
    CREATE CLUSTERED INDEX XIE1cif_modification_history ON cif_modification_history (cifno);

-- Clustered index on ZIPData
IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE object_id = OBJECT_ID('ZIPData') AND name = 'zip_code_index')
    CREATE CLUSTERED INDEX zip_code_index ON ZIPData (ZipCode);
```

This script checks for the existence of each index before creating it, ensuring that there are no duplicate indexes. This is a common practice to avoid errors when deploying SQL scripts multiple times.