Snippet Name: Drop_Index_If_Exists

Tags:

Created Date: 2025-06-16 13:12:43
Last Modified Date: 2025-05-31 10:31:35

Dependencies:

DBA Notes:

Sample Output:

Title: Drop Index If Exists
Author: user
for the following SLQ Server commands, change them to feature IF EXISTS: drop index [CIG__loanacct_statuses-status_code_no-acctrefno-effective_date] on loanacct_statuses; 
drop index [CIG_cif_cifnumber_IDX] on cif; 
drop index [CIG_IX_loanacct_collections_ptp_promise_date_amount_received] on loanacct_collections_ptp; 
drop index [CIG_loanacct_collection_officer_balance_IDX] on loanacct; 
drop index [CIG_loanacct_input_date_IDX] on loanacct; 

drop index [CIG_task_completion_date_IDX] on task; 
drop index [CIG-acctrefno-userdef20_SMS] on loanacct_collections; 
drop index [CIG-IX_campaign_list_detail-campaign_no] on campaign_list_detail; 
drop index [CIG-IX-loanacct_payment_history-nsf_flag-payment_type] on loanacct_payment_history; 
drop index [CIG-IX-loanacct_trans_history-transaction_reference_no] on loanacct_trans_history; 
drop index [CIG-IX-task-task_template_no-NLS_refno] on task; 
drop index [CIG-loanacct_collections-created_SMS] on loanacct_collections; 
drop index [dri_loanacct_restruc_acct_idx] on loanacct; 
drop index [idx_loanacct_statement_804197] on loanacct_statement; 
drop index [IX_LI_campaign_list_detail_campaign_no_owner_uid_status_flag_Includes] on campaign_list_detail; 
drop index [IX_LI_campaign_list_detail_owner_uid_status_flag_Includes] on campaign_list_detail; 
drop index [IX_LI_loanacct_payment_history__payment_type_Includes] on loanacct_payment_history; 
drop index [IX_LI_loanacct_payment_history_payment_amount_userdef05_payment_type] on loanacct_payment_history; 
drop index [IX_LI_loanacct_payment_history_userdef05] on loanacct_payment_history; 
drop index [IX_LI_loanacct_payments_due_acctrefno_transaction_code_Includes] on loanacct_payments_due; 
drop index [IX_LI_loanacct_trans_history__reversal_transrefno_gl_date_Includes] on loanacct_trans_history; 

Author: ChatGPT
Certainly! Here are the SQL Server commands with the `IF EXISTS` feature added:

```sql
DROP INDEX IF EXISTS [CIG__loanacct_statuses-status_code_no-acctrefno-effective_date] ON loanacct_statuses;
DROP INDEX IF EXISTS [CIG_cif_cifnumber_IDX] ON cif;
DROP INDEX IF EXISTS [CIG_IX_loanacct_collections_ptp_promise_date_amount_received] ON loanacct_collections_ptp;
DROP INDEX IF EXISTS [CIG_loanacct_collection_officer_balance_IDX] ON loanacct;
DROP INDEX IF EXISTS [CIG_loanacct_input_date_IDX] ON loanacct;
DROP INDEX IF EXISTS [CIG_task_completion_date_IDX] ON task;
DROP INDEX IF EXISTS [CIG-acctrefno-userdef20_SMS] ON loanacct_collections;
DROP INDEX IF EXISTS [CIG-IX_campaign_list_detail-campaign_no] ON campaign_list_detail;
DROP INDEX IF EXISTS [CIG-IX-loanacct_payment_history-nsf_flag-payment_type] ON loanacct_payment_history;
DROP INDEX IF EXISTS [CIG-IX-loanacct_trans_history-transaction_reference_no] ON loanacct_trans_history;
DROP INDEX IF EXISTS [CIG-IX-task-task_template_no-NLS_refno] ON task;
DROP INDEX IF EXISTS [CIG-loanacct_collections-created_SMS] ON loanacct_collections;
DROP INDEX IF EXISTS [dri_loanacct_restruc_acct_idx] ON loanacct;
DROP INDEX IF EXISTS [idx_loanacct_statement_804197] ON loanacct_statement;
DROP INDEX IF EXISTS [IX_LI_campaign_list_detail_campaign_no_owner_uid_status_flag_Includes] ON campaign_list_detail;
DROP INDEX IF EXISTS [IX_LI_campaign_list_detail_owner_uid_status_flag_Includes] ON campaign_list_detail;
DROP INDEX IF EXISTS [IX_LI_loanacct_payment_history__payment_type_Includes] ON loanacct_payment_history;
DROP INDEX IF EXISTS [IX_LI_loanacct_payment_history_payment_amount_userdef05_payment_type] ON loanacct_payment_history;
DROP INDEX IF EXISTS [IX_LI_loanacct_payment_history_userdef05] ON loanacct_payment_history;
DROP INDEX IF EXISTS [IX_LI_loanacct_payments_due_acctrefno_transaction_code_Includes] ON loanacct_payments_due;
DROP INDEX IF EXISTS [IX_LI_loanacct_trans_history__reversal_transrefno_gl_date_Includes] ON loanacct_trans_history;
```

With this modification, the `DROP INDEX` commands will only execute if the specified index exists on the respective table. If the index doesn't exist, the command will not result in an error.

Author: user
that syntax is for MySQL, i need sql server


Author: ChatGPT
Apologies for the confusion. Here are the SQL Server commands with the `IF EXISTS` feature added:

```sql
DROP INDEX IF EXISTS [CIG__loanacct_statuses-status_code_no-acctrefno-effective_date] ON loanacct_statuses;
DROP INDEX IF EXISTS [CIG_cif_cifnumber_IDX] ON cif;
DROP INDEX IF EXISTS [CIG_IX_loanacct_collections_ptp_promise_date_amount_received] ON loanacct_collections_ptp;
DROP INDEX IF EXISTS [CIG_loanacct_collection_officer_balance_IDX] ON loanacct;
DROP INDEX IF EXISTS [CIG_loanacct_input_date_IDX] ON loanacct;
DROP INDEX IF EXISTS [CIG_task_completion_date_IDX] ON task;
DROP INDEX IF EXISTS [CIG-acctrefno-userdef20_SMS] ON loanacct_collections;
DROP INDEX IF EXISTS [CIG-IX_campaign_list_detail-campaign_no] ON campaign_list_detail;
DROP INDEX IF EXISTS [CIG-IX-loanacct_payment_history-nsf_flag-payment_type] ON loanacct_payment_history;
DROP INDEX IF EXISTS [CIG-IX-loanacct_trans_history-transaction_reference_no] ON loanacct_trans_history;
DROP INDEX IF EXISTS [CIG-IX-task-task_template_no-NLS_refno] ON task;
DROP INDEX IF EXISTS [CIG-loanacct_collections-created_SMS] ON loanacct_collections;
DROP INDEX IF EXISTS [dri_loanacct_restruc_acct_idx] ON loanacct;
DROP INDEX IF EXISTS [idx_loanacct_statement_804197] ON loanacct_statement;
DROP INDEX IF EXISTS [IX_LI_campaign_list_detail_campaign_no_owner_uid_status_flag_Includes] ON campaign_list_detail;
DROP INDEX IF EXISTS [IX_LI_campaign_list_detail_owner_uid_status_flag_Includes] ON campaign_list_detail;
DROP INDEX IF EXISTS [IX_LI_loanacct_payment_history__payment_type_Includes] ON loanacct_payment_history;
DROP INDEX IF EXISTS [IX_LI_loanacct_payment_history_payment_amount_userdef05_payment_type] ON loanacct_payment_history;
DROP INDEX IF EXISTS [IX_LI_loanacct_payment_history_userdef05] ON loanacct_payment_history;
DROP INDEX IF EXISTS [IX_LI_loanacct_payments_due_acctrefno_transaction_code_Includes] ON loanacct_payments_due;
DROP INDEX IF EXISTS [IX_LI_loanacct_trans_history__reversal_transrefno_gl_date_Includes] ON loanacct_trans_history;
```

This syntax should work for SQL Server. It will only drop the index if it exists on the respective table, preventing errors if the index does not exist.

Author: user
please use IF EXISTS(SELECT * FROM sys.indexes) format instead

Author: ChatGPT
Certainly! Here are the SQL Server commands using the `IF EXISTS(SELECT * FROM sys.indexes)` format:

```sql
IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'CIG__loanacct_statuses-status_code_no-acctrefno-effective_date' AND object_id = OBJECT_ID('loanacct_statuses'))
    DROP INDEX [CIG__loanacct_statuses-status_code_no-acctrefno-effective_date] ON loanacct_statuses;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'CIG_cif_cifnumber_IDX' AND object_id = OBJECT_ID('cif'))
    DROP INDEX [CIG_cif_cifnumber_IDX] ON cif;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'CIG_IX_loanacct_collections_ptp_promise_date_amount_received' AND object_id = OBJECT_ID('loanacct_collections_ptp'))
    DROP INDEX [CIG_IX_loanacct_collections_ptp_promise_date_amount_received] ON loanacct_collections_ptp;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'CIG_loanacct_collection_officer_balance_IDX' AND object_id = OBJECT_ID('loanacct'))
    DROP INDEX [CIG_loanacct_collection_officer_balance_IDX] ON loanacct;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'CIG_loanacct_input_date_IDX' AND object_id = OBJECT_ID('loanacct'))
    DROP INDEX [CIG_loanacct_input_date_IDX] ON loanacct;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'CIG_task_completion_date_IDX' AND object_id = OBJECT_ID('task'))
    DROP INDEX [CIG_task_completion_date_IDX] ON task;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'CIG-acctrefno-userdef20_SMS' AND object_id = OBJECT_ID('loanacct_collections'))
    DROP INDEX [CIG-acctrefno-userdef20_SMS] ON loanacct_collections;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'CIG-IX_campaign_list_detail-campaign_no' AND object_id = OBJECT_ID('campaign_list_detail'))
    DROP INDEX [CIG-IX_campaign_list_detail-campaign_no] ON campaign_list_detail;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'CIG-IX-loanacct_payment_history-nsf_flag-payment_type' AND object_id = OBJECT_ID('loanacct_payment_history'))
    DROP INDEX [CIG-IX-loanacct_payment_history-nsf_flag-payment_type] ON loanacct_payment_history;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'CIG-IX-loanacct_trans_history-transaction_reference_no' AND object_id = OBJECT_ID('loanacct_trans_history'))
    DROP INDEX [CIG-IX-loanacct_trans_history-transaction_reference_no] ON loanacct_trans_history;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'CIG-IX-task-task_template_no-NLS_refno' AND object_id = OBJECT_ID('task'))
    DROP INDEX [CIG-IX-task-task_template_no-NLS_refno] ON task;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'CIG-loanacct_collections-created_SMS' AND object_id = OBJECT_ID('loanacct_collections'))
    DROP INDEX [CIG-loanacct_collections-created_SMS] ON loanacct_collections;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'dri_loanacct_restruc_acct_idx' AND object_id = OBJECT_ID('loanacct'))
    DROP INDEX [dri_loanacct_restruc_acct_idx] ON loanacct;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'idx_loanacct_statement_804197' AND object_id = OBJECT_ID('loanacct_statement'))
    DROP INDEX [idx_loanacct_statement_804197] ON loanacct_statement;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'IX_LI_campaign_list_detail_campaign_no_owner_uid_status_flag_Includes' AND object_id = OBJECT_ID('campaign_list_detail'))
    DROP INDEX [IX_LI_campaign_list_detail_campaign_no_owner_uid_status_flag_Includes] ON campaign_list_detail;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'IX_LI_campaign_list_detail_owner_uid_status_flag_Includes' AND object_id = OBJECT_ID('campaign_list_detail'))
    DROP INDEX [IX_LI_campaign_list_detail_owner_uid_status_flag_Includes] ON campaign_list_detail;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'IX_LI_loanacct_payment_history__payment_type_Includes' AND object_id = OBJECT_ID('loanacct_payment_history'))
    DROP INDEX [IX_LI_loanacct_payment_history__payment_type_Includes] ON loanacct_payment_history;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'IX_LI_loanacct_payment_history_payment_amount_userdef05_payment_type' AND object_id = OBJECT_ID('loanacct_payment_history'))
    DROP INDEX [IX_LI_loanacct_payment_history_payment_amount_userdef05_payment_type] ON loanacct_payment_history;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'IX_LI_loanacct_payment_history_userdef05' AND object_id = OBJECT_ID('loanacct_payment_history'))
    DROP INDEX [IX_LI_loanacct_payment_history_userdef05] ON loanacct_payment_history;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'IX_LI_loanacct_payments_due_acctrefno_transaction_code_Includes' AND object_id = OBJECT_ID('loanacct_payments_due'))
    DROP INDEX [IX_LI_loanacct_payments_due_acctrefno_transaction_code_Includes] ON loanacct_payments_due;

IF EXISTS(SELECT * FROM sys.indexes WHERE name = 'IX_LI_loanacct_trans_history__reversal_transrefno_gl_date_Includes' AND object_id = OBJECT_ID('loanacct_trans_history'))
    DROP INDEX [IX_LI_loanacct_trans_history__reversal_transrefno_gl_date_Includes] ON loanacct_trans_history;
```

This format ensures that each index will only be dropped if it exists on the respective table, avoiding errors if the index does not exist.