Create Missing Indexes

Missing Indexes and Statistics objects can impact the overall SQL Server perfromance negatively, therefore creating those objects are benefitial to database workload.


DECLARE @iid int, @oid int, @is_disable bit, @iname nvarchar(128)
DECLARE @Smt nvarchar(max)

USE [intraBiz];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_1_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_1_20190129
ON [intraBiz].[dbo].[sms_erp_inv] (
[act_month],[act_year],[biz_unit],[com_name_erp],[inv_date],[sm_id],[so_no],[sr_id],[src]
) INCLUDE (
[com_code_erp],[company_code],[company_name],[crby],[crdate],[cust_po],[extract_dt],[inv_no],[last_upd],[prj_desc],[total_est_gp],[total_inv],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_1_20190129' AND i.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_erp_inv]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_1_20190129' AND i2.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_erp_inv]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 22
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_1_20190129 ON [intraBiz].[dbo].[sms_erp_inv];
PRINT N'INDEX IX_AIDBA_1_20190129 ON [intraBiz].[dbo].[sms_erp_inv] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraBiz].[dbo].[sms_erp_inv] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraBiz].[dbo].[sms_erp_inv] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_1_20190129 ON [intraBiz].[dbo].[sms_erp_inv];
CREATE NONCLUSTERED INDEX IX_AIDBA_1_20190129
ON [intraBiz].[dbo].[sms_erp_inv] (
[act_month],[act_year],[biz_unit],[com_name_erp],[inv_date],[sm_id],[so_no],[sr_id],[src]
) INCLUDE (
[com_code_erp],[company_code],[company_name],[crby],[crdate],[cust_po],[extract_dt],[inv_no],[last_upd],[prj_desc],[total_est_gp],[total_inv],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_1_20190129 ON [intraBiz].[dbo].[sms_erp_inv] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_1_20190129 in the database.';

USE [intraBiz];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_2_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_2_20190129
ON [intraBiz].[dbo].[sms_erp_inv_breakdown] (
[crby],[crdate],[est_gp],[invoice_value],[principal_id],[product_id],[product_model],[sap_value],[sub2tower_id],[subtower_id],[tower_id]
) INCLUDE (
[inv_no]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_2_20190129' AND i.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_erp_inv_breakdown]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_2_20190129' AND i2.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_erp_inv_breakdown]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 12
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_2_20190129 ON [intraBiz].[dbo].[sms_erp_inv_breakdown];
PRINT N'INDEX IX_AIDBA_2_20190129 ON [intraBiz].[dbo].[sms_erp_inv_breakdown] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraBiz].[dbo].[sms_erp_inv_breakdown] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraBiz].[dbo].[sms_erp_inv_breakdown] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_2_20190129 ON [intraBiz].[dbo].[sms_erp_inv_breakdown];
CREATE NONCLUSTERED INDEX IX_AIDBA_2_20190129
ON [intraBiz].[dbo].[sms_erp_inv_breakdown] (
[crby],[crdate],[est_gp],[invoice_value],[principal_id],[product_id],[product_model],[sap_value],[sub2tower_id],[subtower_id],[tower_id]
) INCLUDE (
[inv_no]) ;

PRINT N'INDEX IX_AIDBA_2_20190129 ON [intraBiz].[dbo].[sms_erp_inv_breakdown] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_2_20190129 in the database.';

USE [intraBiz];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_3_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_3_20190129
ON [intraBiz].[dbo].[sms_erp_product_tower_map] (
[bdm_id],[status],[sub2tower_id],[subtower_id]
) INCLUDE (
[principal_id],[product_hrachy],[product_hrachy_desc],[sr_id],[tech_solution],[tower_id]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_3_20190129' AND i.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_erp_product_tower_map]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_3_20190129' AND i2.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_erp_product_tower_map]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 10
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_3_20190129 ON [intraBiz].[dbo].[sms_erp_product_tower_map];
PRINT N'INDEX IX_AIDBA_3_20190129 ON [intraBiz].[dbo].[sms_erp_product_tower_map] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraBiz].[dbo].[sms_erp_product_tower_map] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraBiz].[dbo].[sms_erp_product_tower_map] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_3_20190129 ON [intraBiz].[dbo].[sms_erp_product_tower_map];
CREATE NONCLUSTERED INDEX IX_AIDBA_3_20190129
ON [intraBiz].[dbo].[sms_erp_product_tower_map] (
[bdm_id],[status],[sub2tower_id],[subtower_id]
) INCLUDE (
[principal_id],[product_hrachy],[product_hrachy_desc],[sr_id],[tech_solution],[tower_id]) ;

PRINT N'INDEX IX_AIDBA_3_20190129 ON [intraBiz].[dbo].[sms_erp_product_tower_map] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_3_20190129 in the database.';

USE [intraBiz];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_4_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_4_20190129
ON [intraBiz].[dbo].[sms_erp_so] (
[act_month],[act_year],[biz_unit],[com_name_erp],[sm_id],[so_date],[sr_id],[src]
) INCLUDE (
[com_code_erp],[company_code],[company_name],[crby],[crdate],[cust_po],[extract_dt],[last_upd],[prj_desc],[so_no],[sr_gp_perc],[total_est_gp],[total_sales],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_4_20190129' AND i.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_erp_so]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_4_20190129' AND i2.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_erp_so]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 22
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_4_20190129 ON [intraBiz].[dbo].[sms_erp_so];
PRINT N'INDEX IX_AIDBA_4_20190129 ON [intraBiz].[dbo].[sms_erp_so] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraBiz].[dbo].[sms_erp_so] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraBiz].[dbo].[sms_erp_so] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_4_20190129 ON [intraBiz].[dbo].[sms_erp_so];
CREATE NONCLUSTERED INDEX IX_AIDBA_4_20190129
ON [intraBiz].[dbo].[sms_erp_so] (
[act_month],[act_year],[biz_unit],[com_name_erp],[sm_id],[so_date],[sr_id],[src]
) INCLUDE (
[com_code_erp],[company_code],[company_name],[crby],[crdate],[cust_po],[extract_dt],[last_upd],[prj_desc],[so_no],[sr_gp_perc],[total_est_gp],[total_sales],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_4_20190129 ON [intraBiz].[dbo].[sms_erp_so] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_4_20190129 in the database.';

USE [intraBiz];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_5_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_5_20190129
ON [intraBiz].[dbo].[sms_erp_so_breakdown] (
[principal_id],[sub2tower_id],[subtower_id],[tower_id]
) INCLUDE (
[est_gp],[pl_code],[product_cat_erp],[product_code_erp],[product_desc_erp],[sales_value],[sap_value],[so_no],[wbse]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_5_20190129' AND i.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_erp_so_breakdown]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_5_20190129' AND i2.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_erp_so_breakdown]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 13
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_5_20190129 ON [intraBiz].[dbo].[sms_erp_so_breakdown];
PRINT N'INDEX IX_AIDBA_5_20190129 ON [intraBiz].[dbo].[sms_erp_so_breakdown] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraBiz].[dbo].[sms_erp_so_breakdown] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraBiz].[dbo].[sms_erp_so_breakdown] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_5_20190129 ON [intraBiz].[dbo].[sms_erp_so_breakdown];
CREATE NONCLUSTERED INDEX IX_AIDBA_5_20190129
ON [intraBiz].[dbo].[sms_erp_so_breakdown] (
[principal_id],[sub2tower_id],[subtower_id],[tower_id]
) INCLUDE (
[est_gp],[pl_code],[product_cat_erp],[product_code_erp],[product_desc_erp],[sales_value],[sap_value],[so_no],[wbse]) ;

PRINT N'INDEX IX_AIDBA_5_20190129 ON [intraBiz].[dbo].[sms_erp_so_breakdown] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_5_20190129 in the database.';

USE [intraBiz];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_6_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_6_20190129
ON [intraBiz].[dbo].[sms_trx_forecast] (
[act_month],[act_year],[fc_month],[sr_id],[status],[win_perc]
) INCLUDE (
[actual_loss_amt],[actual_po_amt],[actual_po_gp],[company_code],[crby],[crdate],[cust_po_no],[fcid],[last_post],[last_upd],[m_id],[no_of_post],[other_competitor],[other_loss_reason],[other_win_strategy],[po_received_dt],[prj_desc],[sap_so_no],[total_gp],[total_gp_perc],[total_sales],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_6_20190129' AND i.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_trx_forecast]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_6_20190129' AND i2.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_trx_forecast]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 28
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_6_20190129 ON [intraBiz].[dbo].[sms_trx_forecast];
PRINT N'INDEX IX_AIDBA_6_20190129 ON [intraBiz].[dbo].[sms_trx_forecast] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraBiz].[dbo].[sms_trx_forecast] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraBiz].[dbo].[sms_trx_forecast] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_6_20190129 ON [intraBiz].[dbo].[sms_trx_forecast];
CREATE NONCLUSTERED INDEX IX_AIDBA_6_20190129
ON [intraBiz].[dbo].[sms_trx_forecast] (
[act_month],[act_year],[fc_month],[sr_id],[status],[win_perc]
) INCLUDE (
[actual_loss_amt],[actual_po_amt],[actual_po_gp],[company_code],[crby],[crdate],[cust_po_no],[fcid],[last_post],[last_upd],[m_id],[no_of_post],[other_competitor],[other_loss_reason],[other_win_strategy],[po_received_dt],[prj_desc],[sap_so_no],[total_gp],[total_gp_perc],[total_sales],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_6_20190129 ON [intraBiz].[dbo].[sms_trx_forecast] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_6_20190129 in the database.';

USE [intraBiz];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_7_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_7_20190129
ON [intraBiz].[dbo].[sms_trx_forecast_breakdown] (
[principal_id],[sub2tower_id],[subtower_id],[tower_id]
) INCLUDE (
[crby],[crdate],[fcid],[product_model],[rev_type],[sales_amt],[sales_gp],[sales_gp_perc]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_7_20190129' AND i.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_trx_forecast_breakdown]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_7_20190129' AND i2.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_trx_forecast_breakdown]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 12
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_7_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_breakdown];
PRINT N'INDEX IX_AIDBA_7_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_breakdown] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraBiz].[dbo].[sms_trx_forecast_breakdown] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraBiz].[dbo].[sms_trx_forecast_breakdown] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_7_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_breakdown];
CREATE NONCLUSTERED INDEX IX_AIDBA_7_20190129
ON [intraBiz].[dbo].[sms_trx_forecast_breakdown] (
[principal_id],[sub2tower_id],[subtower_id],[tower_id]
) INCLUDE (
[crby],[crdate],[fcid],[product_model],[rev_type],[sales_amt],[sales_gp],[sales_gp_perc]) ;

PRINT N'INDEX IX_AIDBA_7_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_breakdown] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_7_20190129 in the database.';

USE [intraBiz];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_8_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_8_20190129
ON [intraBiz].[dbo].[sms_trx_forecast_breakdown_posted] (
[principal_id],[sub2tower_id],[subtower_id],[tower_id]
) INCLUDE (
[p_fcid],[sales_amt],[sales_gp]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_8_20190129' AND i.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_trx_forecast_breakdown_posted]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_8_20190129' AND i2.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_trx_forecast_breakdown_posted]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 7
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_8_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_breakdown_posted];
PRINT N'INDEX IX_AIDBA_8_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_breakdown_posted] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraBiz].[dbo].[sms_trx_forecast_breakdown_posted] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraBiz].[dbo].[sms_trx_forecast_breakdown_posted] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_8_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_breakdown_posted];
CREATE NONCLUSTERED INDEX IX_AIDBA_8_20190129
ON [intraBiz].[dbo].[sms_trx_forecast_breakdown_posted] (
[principal_id],[sub2tower_id],[subtower_id],[tower_id]
) INCLUDE (
[p_fcid],[sales_amt],[sales_gp]) ;

PRINT N'INDEX IX_AIDBA_8_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_breakdown_posted] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_8_20190129 in the database.';

USE [intraBiz];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_9_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_9_20190129
ON [intraBiz].[dbo].[sms_trx_forecast_inv] (
[act_month],[act_year],[fcid],[fcinv_month],[status],[win_perc]
) INCLUDE (
[crby],[crdate],[fcinv_id],[inv_amt],[inv_gp],[inv_gp_perc],[last_post],[last_upd],[m_id],[no_of_post],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_9_20190129' AND i.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_trx_forecast_inv]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_9_20190129' AND i2.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_trx_forecast_inv]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 17
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_9_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_inv];
PRINT N'INDEX IX_AIDBA_9_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_inv] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraBiz].[dbo].[sms_trx_forecast_inv] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraBiz].[dbo].[sms_trx_forecast_inv] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_9_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_inv];
CREATE NONCLUSTERED INDEX IX_AIDBA_9_20190129
ON [intraBiz].[dbo].[sms_trx_forecast_inv] (
[act_month],[act_year],[fcid],[fcinv_month],[status],[win_perc]
) INCLUDE (
[crby],[crdate],[fcinv_id],[inv_amt],[inv_gp],[inv_gp_perc],[last_post],[last_upd],[m_id],[no_of_post],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_9_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_inv] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_9_20190129 in the database.';

USE [intraBiz];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_10_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_10_20190129
ON [intraBiz].[dbo].[sms_trx_forecast_inv_posted] (
[act_month],[act_year],[fcinv_month],[p_month],[p_year],[status],[win_perc]
) INCLUDE (
[fcid],[fcinv_id],[inv_amt],[inv_gp],[inv_gp_perc],[p_fcinv_id],[posted_date]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_10_20190129' AND i.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_trx_forecast_inv_posted]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_10_20190129' AND i2.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_trx_forecast_inv_posted]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 14
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_10_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_inv_posted];
PRINT N'INDEX IX_AIDBA_10_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_inv_posted] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraBiz].[dbo].[sms_trx_forecast_inv_posted] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraBiz].[dbo].[sms_trx_forecast_inv_posted] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_10_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_inv_posted];
CREATE NONCLUSTERED INDEX IX_AIDBA_10_20190129
ON [intraBiz].[dbo].[sms_trx_forecast_inv_posted] (
[act_month],[act_year],[fcinv_month],[p_month],[p_year],[status],[win_perc]
) INCLUDE (
[fcid],[fcinv_id],[inv_amt],[inv_gp],[inv_gp_perc],[p_fcinv_id],[posted_date]) ;

PRINT N'INDEX IX_AIDBA_10_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_inv_posted] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_10_20190129 in the database.';

USE [intraBiz];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_11_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_11_20190129
ON [intraBiz].[dbo].[sms_trx_forecast_posted] (
[act_month],[act_year],[fc_month],[p_month],[p_year],[sr_id],[status],[win_perc]
) INCLUDE (
[company_code],[fcid],[other_competitor],[other_win_strategy],[p_fcid],[posted_date],[prj_desc],[total_gp],[total_gp_perc],[total_sales]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_11_20190129' AND i.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_trx_forecast_posted]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_11_20190129' AND i2.object_id = OBJECT_ID('[intraBiz].[dbo].[sms_trx_forecast_posted]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 18
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_11_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_posted];
PRINT N'INDEX IX_AIDBA_11_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_posted] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraBiz].[dbo].[sms_trx_forecast_posted] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraBiz].[dbo].[sms_trx_forecast_posted] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_11_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_posted];
CREATE NONCLUSTERED INDEX IX_AIDBA_11_20190129
ON [intraBiz].[dbo].[sms_trx_forecast_posted] (
[act_month],[act_year],[fc_month],[p_month],[p_year],[sr_id],[status],[win_perc]
) INCLUDE (
[company_code],[fcid],[other_competitor],[other_win_strategy],[p_fcid],[posted_date],[prj_desc],[total_gp],[total_gp_perc],[total_sales]) ;

PRINT N'INDEX IX_AIDBA_11_20190129 ON [intraBiz].[dbo].[sms_trx_forecast_posted] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_11_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_12_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_12_20190129
ON [intraCSSG].[dbo].[fs_contract_billing_breakdown] (
[contract_id],[inv_no],[ref_id],[status]
) INCLUDE (
[edate],[sdate]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_12_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_contract_billing_breakdown]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_12_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_contract_billing_breakdown]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 6
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_12_20190129 ON [intraCSSG].[dbo].[fs_contract_billing_breakdown];
PRINT N'INDEX IX_AIDBA_12_20190129 ON [intraCSSG].[dbo].[fs_contract_billing_breakdown] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_contract_billing_breakdown] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_contract_billing_breakdown] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_12_20190129 ON [intraCSSG].[dbo].[fs_contract_billing_breakdown];
CREATE NONCLUSTERED INDEX IX_AIDBA_12_20190129
ON [intraCSSG].[dbo].[fs_contract_billing_breakdown] (
[contract_id],[inv_no],[ref_id],[status]
) INCLUDE (
[edate],[sdate]) ;

PRINT N'INDEX IX_AIDBA_12_20190129 ON [intraCSSG].[dbo].[fs_contract_billing_breakdown] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_12_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_13_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_13_20190129
ON [intraCSSG].[dbo].[fs_contract_equipment] (
[contract_id]
) INCLUDE (
[address],[b2b_flag],[b2b_id],[charge],[crby],[crdate],[equip_brand],[equip_category],[equip_desc],[equip_id],[equip_model],[equip_os],[equip_os_vendor],[equip_sn],[equip_subcat],[equip_sw],[last_upd],[priority_id],[remark],[service_dept],[sign_hour],[sign_incident],[supp_hour],[supp_hour_rmk],[sw_qty],[upd_by],[vendor_expiry],[warranty_vendor]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_13_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_contract_equipment]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_13_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_contract_equipment]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 29
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_13_20190129 ON [intraCSSG].[dbo].[fs_contract_equipment];
PRINT N'INDEX IX_AIDBA_13_20190129 ON [intraCSSG].[dbo].[fs_contract_equipment] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_contract_equipment] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_contract_equipment] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_13_20190129 ON [intraCSSG].[dbo].[fs_contract_equipment];
CREATE NONCLUSTERED INDEX IX_AIDBA_13_20190129
ON [intraCSSG].[dbo].[fs_contract_equipment] (
[contract_id]
) INCLUDE (
[address],[b2b_flag],[b2b_id],[charge],[crby],[crdate],[equip_brand],[equip_category],[equip_desc],[equip_id],[equip_model],[equip_os],[equip_os_vendor],[equip_sn],[equip_subcat],[equip_sw],[last_upd],[priority_id],[remark],[service_dept],[sign_hour],[sign_incident],[supp_hour],[supp_hour_rmk],[sw_qty],[upd_by],[vendor_expiry],[warranty_vendor]) ;

PRINT N'INDEX IX_AIDBA_13_20190129 ON [intraCSSG].[dbo].[fs_contract_equipment] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_13_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_14_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_14_20190129
ON [intraCSSG].[dbo].[fs_contract_master] (
[approval_status],[contract_edate],[contract_status],[contract_type],[erp_cust_name],[org_code],[pl_code],[renew_flag],[sr_id]
) INCLUDE (
[annual_amt],[auto_renewal],[b2b_flag],[bsd_flag],[contract_amt],[contract_desc],[contract_id],[contract_name],[contract_sdate],[control_status],[crdate],[crrb_flag],[currency],[cust_po_gst],[cust_po_no],[cust_so_no],[erp_cust_no],[support_cust_name]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_14_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_contract_master]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_14_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_contract_master]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 27
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_14_20190129 ON [intraCSSG].[dbo].[fs_contract_master];
PRINT N'INDEX IX_AIDBA_14_20190129 ON [intraCSSG].[dbo].[fs_contract_master] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_contract_master] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_contract_master] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_14_20190129 ON [intraCSSG].[dbo].[fs_contract_master];
CREATE NONCLUSTERED INDEX IX_AIDBA_14_20190129
ON [intraCSSG].[dbo].[fs_contract_master] (
[approval_status],[contract_edate],[contract_status],[contract_type],[erp_cust_name],[org_code],[pl_code],[renew_flag],[sr_id]
) INCLUDE (
[annual_amt],[auto_renewal],[b2b_flag],[bsd_flag],[contract_amt],[contract_desc],[contract_id],[contract_name],[contract_sdate],[control_status],[crdate],[crrb_flag],[currency],[cust_po_gst],[cust_po_no],[cust_so_no],[erp_cust_no],[support_cust_name]) ;

PRINT N'INDEX IX_AIDBA_14_20190129 ON [intraCSSG].[dbo].[fs_contract_master] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_14_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_15_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_15_20190129
ON [intraCSSG].[dbo].[fs_customer_branch] (
[region_id]
) INCLUDE (
[address1],[address2],[branch_id],[branch_name],[charge_code],[city],[contact_person],[country],[crby],[crdate],[email],[fax_no],[last_upd],[mileage],[postcode],[remark],[state],[status],[support_grp],[tel_no],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_15_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_customer_branch]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_15_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_customer_branch]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 22
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_15_20190129 ON [intraCSSG].[dbo].[fs_customer_branch];
PRINT N'INDEX IX_AIDBA_15_20190129 ON [intraCSSG].[dbo].[fs_customer_branch] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_customer_branch] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_customer_branch] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_15_20190129 ON [intraCSSG].[dbo].[fs_customer_branch];
CREATE NONCLUSTERED INDEX IX_AIDBA_15_20190129
ON [intraCSSG].[dbo].[fs_customer_branch] (
[region_id]
) INCLUDE (
[address1],[address2],[branch_id],[branch_name],[charge_code],[city],[contact_person],[country],[crby],[crdate],[email],[fax_no],[last_upd],[mileage],[postcode],[remark],[state],[status],[support_grp],[tel_no],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_15_20190129 ON [intraCSSG].[dbo].[fs_customer_branch] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_15_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_16_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_16_20190129
ON [intraCSSG].[dbo].[fs_customer_master] (
[status]
) INCLUDE (
[cust_id],[cust_name]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_16_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_customer_master]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_16_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_customer_master]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 3
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_16_20190129 ON [intraCSSG].[dbo].[fs_customer_master];
PRINT N'INDEX IX_AIDBA_16_20190129 ON [intraCSSG].[dbo].[fs_customer_master] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_customer_master] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_customer_master] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_16_20190129 ON [intraCSSG].[dbo].[fs_customer_master];
CREATE NONCLUSTERED INDEX IX_AIDBA_16_20190129
ON [intraCSSG].[dbo].[fs_customer_master] (
[status]
) INCLUDE (
[cust_id],[cust_name]) ;

PRINT N'INDEX IX_AIDBA_16_20190129 ON [intraCSSG].[dbo].[fs_customer_master] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_16_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_17_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_17_20190129
ON [intraCSSG].[dbo].[fs_dell_trx_closure] (
[interface_flag],[manifest_no],[ser_no],[upload_flag]
) INCLUDE (
[ticket_id]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_17_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_dell_trx_closure]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_17_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_dell_trx_closure]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 5
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_17_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_closure];
PRINT N'INDEX IX_AIDBA_17_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_closure] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_dell_trx_closure] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_dell_trx_closure] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_17_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_closure];
CREATE NONCLUSTERED INDEX IX_AIDBA_17_20190129
ON [intraCSSG].[dbo].[fs_dell_trx_closure] (
[interface_flag],[manifest_no],[ser_no],[upload_flag]
) INCLUDE (
[ticket_id]) ;

PRINT N'INDEX IX_AIDBA_17_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_closure] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_17_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_18_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_18_20190129
ON [intraCSSG].[dbo].[fs_dell_trx_ecaf] (
[guid],[ticket_id]
) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_18_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_dell_trx_ecaf]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_18_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_dell_trx_ecaf]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 2
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_18_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_ecaf];
PRINT N'INDEX IX_AIDBA_18_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_ecaf] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_dell_trx_ecaf] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_dell_trx_ecaf] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_18_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_ecaf];
CREATE NONCLUSTERED INDEX IX_AIDBA_18_20190129
ON [intraCSSG].[dbo].[fs_dell_trx_ecaf] (
[guid],[ticket_id]
);

PRINT N'INDEX IX_AIDBA_18_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_ecaf] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_18_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_19_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_19_20190129
ON [intraCSSG].[dbo].[fs_dell_trx_part_rec] (
[crby],[crdate],[pr_status],[rec_dt],[serv_dept],[ticket_id]
) INCLUDE (
[last_upd],[pr_id],[pr_issue_by],[pr_issue_dt],[pr_issue_to],[pr_part_no],[pr_qty],[pr_return_dt],[pr_uid],[ser_no],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_19_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_dell_trx_part_rec]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_19_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_dell_trx_part_rec]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 17
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_19_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_part_rec];
PRINT N'INDEX IX_AIDBA_19_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_part_rec] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_dell_trx_part_rec] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_dell_trx_part_rec] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_19_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_part_rec];
CREATE NONCLUSTERED INDEX IX_AIDBA_19_20190129
ON [intraCSSG].[dbo].[fs_dell_trx_part_rec] (
[crby],[crdate],[pr_status],[rec_dt],[serv_dept],[ticket_id]
) INCLUDE (
[last_upd],[pr_id],[pr_issue_by],[pr_issue_dt],[pr_issue_to],[pr_part_no],[pr_qty],[pr_return_dt],[pr_uid],[ser_no],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_19_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_part_rec] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_19_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_20_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_20_20190129
ON [intraCSSG].[dbo].[fs_dell_trx_rc] (
[crdate],[interface_flag],[rc_code],[ref_no],[ser_no],[upd_type]
) INCLUDE (
[comments],[engineer_info],[eta_dt],[onsite_dt],[ticket_id]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_20_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_dell_trx_rc]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_20_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_dell_trx_rc]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 11
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_20_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_rc];
PRINT N'INDEX IX_AIDBA_20_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_rc] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_dell_trx_rc] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_dell_trx_rc] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_20_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_rc];
CREATE NONCLUSTERED INDEX IX_AIDBA_20_20190129
ON [intraCSSG].[dbo].[fs_dell_trx_rc] (
[crdate],[interface_flag],[rc_code],[ref_no],[ser_no],[upd_type]
) INCLUDE (
[comments],[engineer_info],[eta_dt],[onsite_dt],[ticket_id]) ;

PRINT N'INDEX IX_AIDBA_20_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_rc] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_20_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_21_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_21_20190129
ON [intraCSSG].[dbo].[fs_dell_trx_status] (
[completed_dt],[interface_flag],[upd_type]
) INCLUDE (
[ser_no],[ticket_id]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_21_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_dell_trx_status]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_21_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_dell_trx_status]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 5
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_21_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_status];
PRINT N'INDEX IX_AIDBA_21_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_status] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_dell_trx_status] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_dell_trx_status] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_21_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_status];
CREATE NONCLUSTERED INDEX IX_AIDBA_21_20190129
ON [intraCSSG].[dbo].[fs_dell_trx_status] (
[completed_dt],[interface_flag],[upd_type]
) INCLUDE (
[ser_no],[ticket_id]) ;

PRINT N'INDEX IX_AIDBA_21_20190129 ON [intraCSSG].[dbo].[fs_dell_trx_status] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_21_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_22_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_22_20190129
ON [intraCSSG].[dbo].[fs_service_engineer] (
[alt_email],[crdate],[dcse_no],[device_model],[last_upd],[pager_no],[service_dept],[status],[svc_3g],[svc_gprs],[svc_mms],[upd_by]
) INCLUDE (
[nickname],[se_id]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_22_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_service_engineer]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_22_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_service_engineer]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 14
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_22_20190129 ON [intraCSSG].[dbo].[fs_service_engineer];
PRINT N'INDEX IX_AIDBA_22_20190129 ON [intraCSSG].[dbo].[fs_service_engineer] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_service_engineer] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_service_engineer] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_22_20190129 ON [intraCSSG].[dbo].[fs_service_engineer];
CREATE NONCLUSTERED INDEX IX_AIDBA_22_20190129
ON [intraCSSG].[dbo].[fs_service_engineer] (
[alt_email],[crdate],[dcse_no],[device_model],[last_upd],[pager_no],[service_dept],[status],[svc_3g],[svc_gprs],[svc_mms],[upd_by]
) INCLUDE (
[nickname],[se_id]) ;

PRINT N'INDEX IX_AIDBA_22_20190129 ON [intraCSSG].[dbo].[fs_service_engineer] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_22_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_23_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_23_20190129
ON [intraCSSG].[dbo].[fs_stock_master] (
[loc_store],[movement_status],[part_category]
) INCLUDE (
[avail_date],[batch_no],[contra_with],[crby],[crdate],[init_cost],[item_id],[last_upd],[loc_aisle],[loc_rack],[loc_zone],[movement_date],[part_brand],[part_desc],[part_no],[purchase_date],[qty],[quotation_ref],[remark],[sap_po_no],[serial_no],[soldto_price],[supp_flag],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_23_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_stock_master]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_23_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_stock_master]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 27
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_23_20190129 ON [intraCSSG].[dbo].[fs_stock_master];
PRINT N'INDEX IX_AIDBA_23_20190129 ON [intraCSSG].[dbo].[fs_stock_master] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_stock_master] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_stock_master] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_23_20190129 ON [intraCSSG].[dbo].[fs_stock_master];
CREATE NONCLUSTERED INDEX IX_AIDBA_23_20190129
ON [intraCSSG].[dbo].[fs_stock_master] (
[loc_store],[movement_status],[part_category]
) INCLUDE (
[avail_date],[batch_no],[contra_with],[crby],[crdate],[init_cost],[item_id],[last_upd],[loc_aisle],[loc_rack],[loc_zone],[movement_date],[part_brand],[part_desc],[part_no],[purchase_date],[qty],[quotation_ref],[remark],[sap_po_no],[serial_no],[soldto_price],[supp_flag],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_23_20190129 ON [intraCSSG].[dbo].[fs_stock_master] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_23_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_24_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_24_20190129
ON [intraCSSG].[dbo].[fs_trx_delivery_temp_dept] (
[temp_det_id]
) INCLUDE (
[temp_dv_to]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_24_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_delivery_temp_dept]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_24_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_delivery_temp_dept]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 2
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_24_20190129 ON [intraCSSG].[dbo].[fs_trx_delivery_temp_dept];
PRINT N'INDEX IX_AIDBA_24_20190129 ON [intraCSSG].[dbo].[fs_trx_delivery_temp_dept] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_delivery_temp_dept] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_delivery_temp_dept] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_24_20190129 ON [intraCSSG].[dbo].[fs_trx_delivery_temp_dept];
CREATE NONCLUSTERED INDEX IX_AIDBA_24_20190129
ON [intraCSSG].[dbo].[fs_trx_delivery_temp_dept] (
[temp_det_id]
) INCLUDE (
[temp_dv_to]) ;

PRINT N'INDEX IX_AIDBA_24_20190129 ON [intraCSSG].[dbo].[fs_trx_delivery_temp_dept] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_24_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_25_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_25_20190129
ON [intraCSSG].[dbo].[fs_trx_delivery_track] (
[crby],[dv_from],[dv_req_dt],[dv_status],[dv_to],[temp_id]
) INCLUDE (
[crdate],[dv_add],[dv_att],[dv_consign],[dv_consign_by],[dv_consign_dt],[dv_id],[dv_item_name],[dv_purpose],[dv_qty],[dv_rec],[dv_rec_by],[dv_rec_dt],[dv_ref_id],[dv_remark],[dv_req_by],[dv_return],[dv_sn],[dv_urgency],[last_upd],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_25_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_delivery_track]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_25_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_delivery_track]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 27
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_25_20190129 ON [intraCSSG].[dbo].[fs_trx_delivery_track];
PRINT N'INDEX IX_AIDBA_25_20190129 ON [intraCSSG].[dbo].[fs_trx_delivery_track] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_delivery_track] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_delivery_track] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_25_20190129 ON [intraCSSG].[dbo].[fs_trx_delivery_track];
CREATE NONCLUSTERED INDEX IX_AIDBA_25_20190129
ON [intraCSSG].[dbo].[fs_trx_delivery_track] (
[crby],[dv_from],[dv_req_dt],[dv_status],[dv_to],[temp_id]
) INCLUDE (
[crdate],[dv_add],[dv_att],[dv_consign],[dv_consign_by],[dv_consign_dt],[dv_id],[dv_item_name],[dv_purpose],[dv_qty],[dv_rec],[dv_rec_by],[dv_rec_dt],[dv_ref_id],[dv_remark],[dv_req_by],[dv_return],[dv_sn],[dv_urgency],[last_upd],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_25_20190129 ON [intraCSSG].[dbo].[fs_trx_delivery_track] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_25_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_26_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_26_20190129
ON [intraCSSG].[dbo].[fs_trx_invoice_master] (
[approval_status],[crdate],[erp_cust_no],[inv_type],[org_code],[park_or_post],[pl_code],[post_status]
) INCLUDE (
[address],[approval_by],[approval_date],[att_name],[cn_dt],[cn_no],[company_code],[contract_id],[crby],[currency],[currency_rate],[cust_id],[cust_po_no],[erp_cust_name],[fax_no],[fsr_date],[gl_code],[gst_amt],[int_req_no],[inv_date],[inv_no],[invoice_amt],[last_print_dt],[last_upd],[late_reason],[local_amt],[no_of_print],[payment_status],[payment_term],[post_dt],[print_receipt],[print_status],[remaining_amt],[remarks],[service_tax],[sr_id],[tax_code],[tel_no],[ticket_id],[upd_by],[wbse]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_26_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_invoice_master]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_26_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_invoice_master]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 49
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_26_20190129 ON [intraCSSG].[dbo].[fs_trx_invoice_master];
PRINT N'INDEX IX_AIDBA_26_20190129 ON [intraCSSG].[dbo].[fs_trx_invoice_master] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_invoice_master] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_invoice_master] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_26_20190129 ON [intraCSSG].[dbo].[fs_trx_invoice_master];
CREATE NONCLUSTERED INDEX IX_AIDBA_26_20190129
ON [intraCSSG].[dbo].[fs_trx_invoice_master] (
[approval_status],[crdate],[erp_cust_no],[inv_type],[org_code],[park_or_post],[pl_code],[post_status]
) INCLUDE (
[address],[approval_by],[approval_date],[att_name],[cn_dt],[cn_no],[company_code],[contract_id],[crby],[currency],[currency_rate],[cust_id],[cust_po_no],[erp_cust_name],[fax_no],[fsr_date],[gl_code],[gst_amt],[int_req_no],[inv_date],[inv_no],[invoice_amt],[last_print_dt],[last_upd],[late_reason],[local_amt],[no_of_print],[payment_status],[payment_term],[post_dt],[print_receipt],[print_status],[remaining_amt],[remarks],[service_tax],[sr_id],[tax_code],[tel_no],[ticket_id],[upd_by],[wbse]) ;

PRINT N'INDEX IX_AIDBA_26_20190129 ON [intraCSSG].[dbo].[fs_trx_invoice_master] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_26_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_27_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_27_20190129
ON [intraCSSG].[dbo].[fs_trx_nonticket_req] (
[req_by],[req_dt],[req_id],[serv_dept],[status]
) INCLUDE (
[approve_by],[approve_dt],[crby],[crdate],[email_flag],[last_upd],[print_flag],[rejected_reason],[superior],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_27_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_nonticket_req]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_27_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_nonticket_req]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 15
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_27_20190129 ON [intraCSSG].[dbo].[fs_trx_nonticket_req];
PRINT N'INDEX IX_AIDBA_27_20190129 ON [intraCSSG].[dbo].[fs_trx_nonticket_req] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_nonticket_req] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_nonticket_req] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_27_20190129 ON [intraCSSG].[dbo].[fs_trx_nonticket_req];
CREATE NONCLUSTERED INDEX IX_AIDBA_27_20190129
ON [intraCSSG].[dbo].[fs_trx_nonticket_req] (
[req_by],[req_dt],[req_id],[serv_dept],[status]
) INCLUDE (
[approve_by],[approve_dt],[crby],[crdate],[email_flag],[last_upd],[print_flag],[rejected_reason],[superior],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_27_20190129 ON [intraCSSG].[dbo].[fs_trx_nonticket_req] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_27_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_28_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_28_20190129
ON [intraCSSG].[dbo].[fs_trx_nonticket_req_consign] (
[crdate]
) INCLUDE (
[csign_notes],[req_det_id],[req_id]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_28_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_nonticket_req_consign]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_28_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_nonticket_req_consign]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 4
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_28_20190129 ON [intraCSSG].[dbo].[fs_trx_nonticket_req_consign];
PRINT N'INDEX IX_AIDBA_28_20190129 ON [intraCSSG].[dbo].[fs_trx_nonticket_req_consign] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_nonticket_req_consign] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_nonticket_req_consign] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_28_20190129 ON [intraCSSG].[dbo].[fs_trx_nonticket_req_consign];
CREATE NONCLUSTERED INDEX IX_AIDBA_28_20190129
ON [intraCSSG].[dbo].[fs_trx_nonticket_req_consign] (
[crdate]
) INCLUDE (
[csign_notes],[req_det_id],[req_id]) ;

PRINT N'INDEX IX_AIDBA_28_20190129 ON [intraCSSG].[dbo].[fs_trx_nonticket_req_consign] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_28_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_29_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_29_20190129
ON [intraCSSG].[dbo].[fs_trx_nonticket_req_det] (
[req_det_id],[req_id],[si_return],[spare_status],[status]
) INCLUDE (
[col_by],[crby],[crdate],[issue_by],[issue_dt],[issue_item],[last_upd],[loan_flag],[order_eta],[order_po_no],[rejected_reason],[sc_category],[sc_item_name],[sc_item_sn],[sc_item_to],[sc_part_no],[sc_remarks],[si_category],[si_item_from],[si_item_name],[si_item_sn],[si_part_eta],[si_part_no],[si_purpose],[si_qty],[si_remarks],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_29_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_nonticket_req_det]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_29_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_nonticket_req_det]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 32
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_29_20190129 ON [intraCSSG].[dbo].[fs_trx_nonticket_req_det];
PRINT N'INDEX IX_AIDBA_29_20190129 ON [intraCSSG].[dbo].[fs_trx_nonticket_req_det] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_nonticket_req_det] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_nonticket_req_det] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_29_20190129 ON [intraCSSG].[dbo].[fs_trx_nonticket_req_det];
CREATE NONCLUSTERED INDEX IX_AIDBA_29_20190129
ON [intraCSSG].[dbo].[fs_trx_nonticket_req_det] (
[req_det_id],[req_id],[si_return],[spare_status],[status]
) INCLUDE (
[col_by],[crby],[crdate],[issue_by],[issue_dt],[issue_item],[last_upd],[loan_flag],[order_eta],[order_po_no],[rejected_reason],[sc_category],[sc_item_name],[sc_item_sn],[sc_item_to],[sc_part_no],[sc_remarks],[si_category],[si_item_from],[si_item_name],[si_item_sn],[si_part_eta],[si_part_no],[si_purpose],[si_qty],[si_remarks],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_29_20190129 ON [intraCSSG].[dbo].[fs_trx_nonticket_req_det] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_29_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_30_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_30_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_assign] (
[assign_type]
) INCLUDE (
[ta_id],[ticket_id]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_30_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_assign]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_30_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_assign]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 3
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_30_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_assign];
PRINT N'INDEX IX_AIDBA_30_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_assign] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_assign] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_assign] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_30_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_assign];
CREATE NONCLUSTERED INDEX IX_AIDBA_30_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_assign] (
[assign_type]
) INCLUDE (
[ta_id],[ticket_id]) ;

PRINT N'INDEX IX_AIDBA_30_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_assign] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_30_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_31_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_31_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_assign_staff] (
[se_id]
) INCLUDE (
[last_upd],[ta_id],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_31_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_assign_staff]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_31_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_assign_staff]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 4
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_31_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_assign_staff];
PRINT N'INDEX IX_AIDBA_31_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_assign_staff] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_assign_staff] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_assign_staff] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_31_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_assign_staff];
CREATE NONCLUSTERED INDEX IX_AIDBA_31_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_assign_staff] (
[se_id]
) INCLUDE (
[last_upd],[ta_id],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_31_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_assign_staff] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_31_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_32_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_32_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_expense] (
[se_id],[ta_id],[ticket_id],[transport_type1],[wca_upload_flag]
) INCLUDE (
[crby],[crdate],[hotel_flag],[last_upd],[location_from],[location_next],[ot_flag],[ot_from],[ot_rate],[ot_to],[parking],[tact_id],[taxi],[toll_fee],[total_mileage],[trnspt_gst],[trnspt_supp_gstid],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_32_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_expense]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_32_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_expense]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 23
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_32_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_expense];
PRINT N'INDEX IX_AIDBA_32_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_expense] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_expense] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_expense] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_32_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_expense];
CREATE NONCLUSTERED INDEX IX_AIDBA_32_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_expense] (
[se_id],[ta_id],[ticket_id],[transport_type1],[wca_upload_flag]
) INCLUDE (
[crby],[crdate],[hotel_flag],[last_upd],[location_from],[location_next],[ot_flag],[ot_from],[ot_rate],[ot_to],[parking],[tact_id],[taxi],[toll_fee],[total_mileage],[trnspt_gst],[trnspt_supp_gstid],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_32_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_expense] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_32_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_33_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_33_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_fsr] (
[activity_type],[commence_dt],[ref_id],[ta_id]
) INCLUDE (
[complete_dt],[departure_dt],[etes_id],[fsr_no],[hw_ready_dt],[next_action],[ticket_id]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_33_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_fsr]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_33_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_fsr]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 11
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_33_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_fsr];
PRINT N'INDEX IX_AIDBA_33_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_fsr] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_fsr] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_fsr] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_33_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_fsr];
CREATE NONCLUSTERED INDEX IX_AIDBA_33_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_fsr] (
[activity_type],[commence_dt],[ref_id],[ta_id]
) INCLUDE (
[complete_dt],[departure_dt],[etes_id],[fsr_no],[hw_ready_dt],[next_action],[ticket_id]) ;

PRINT N'INDEX IX_AIDBA_33_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_fsr] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_33_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_34_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_34_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_master] (
[branch_id],[cust_ref],[priority_id],[received_dt],[service_dept],[status],[ticket_status],[ticket_type]
) INCLUDE (
[callback_dt],[closed_dt],[region_id],[reschedule_dt],[rpt_by_address],[schedule_dt],[site_contact],[site_phone],[ticket_id]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_34_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_master]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_34_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_master]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 17
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_34_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_master];
PRINT N'INDEX IX_AIDBA_34_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_master] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_master] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_master] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_34_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_master];
CREATE NONCLUSTERED INDEX IX_AIDBA_34_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_master] (
[branch_id],[cust_ref],[priority_id],[received_dt],[service_dept],[status],[ticket_status],[ticket_type]
) INCLUDE (
[callback_dt],[closed_dt],[region_id],[reschedule_dt],[rpt_by_address],[schedule_dt],[site_contact],[site_phone],[ticket_id]) ;

PRINT N'INDEX IX_AIDBA_34_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_master] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_34_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_35_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_35_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_pcall_quote] (
[quote_status],[service_dept],[tquot_id]
) INCLUDE (
[approved_by],[approved_dt],[asset_tag],[attention],[auth_type],[coord_dept],[coord_name],[coord_position],[crby],[crdate],[csam_add],[csam_fax_no],[csam_ref_no],[cust_authority],[cust_fax_no],[cust_ref_no],[cust_tel_no],[customer_name],[dept_att],[dept_cc_branch],[equip_model],[faulty_desc],[gst_flag],[inv_status],[last_upd],[manager_dept],[manager_name],[manager_position],[min_trnspt],[min_work],[nopart_period],[part_avai],[payment_term],[prepared_by],[prepared_dt],[qtype],[quote_file_loc],[quote_gst_amt],[quote_total_amt],[quote_validity],[remark],[sent],[sent_dt],[serial_number],[ticket_id],[upd_by],[warranty_period]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_35_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_pcall_quote]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_35_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_pcall_quote]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 50
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_35_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_pcall_quote];
PRINT N'INDEX IX_AIDBA_35_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_pcall_quote] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_pcall_quote] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_pcall_quote] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_35_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_pcall_quote];
CREATE NONCLUSTERED INDEX IX_AIDBA_35_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_pcall_quote] (
[quote_status],[service_dept],[tquot_id]
) INCLUDE (
[approved_by],[approved_dt],[asset_tag],[attention],[auth_type],[coord_dept],[coord_name],[coord_position],[crby],[crdate],[csam_add],[csam_fax_no],[csam_ref_no],[cust_authority],[cust_fax_no],[cust_ref_no],[cust_tel_no],[customer_name],[dept_att],[dept_cc_branch],[equip_model],[faulty_desc],[gst_flag],[inv_status],[last_upd],[manager_dept],[manager_name],[manager_position],[min_trnspt],[min_work],[nopart_period],[part_avai],[payment_term],[prepared_by],[prepared_dt],[qtype],[quote_file_loc],[quote_gst_amt],[quote_total_amt],[quote_validity],[remark],[sent],[sent_dt],[serial_number],[ticket_id],[upd_by],[warranty_period]) ;

PRINT N'INDEX IX_AIDBA_35_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_pcall_quote] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_35_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_36_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_36_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_priority_hist] (
[ticket_id]
) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_36_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_priority_hist]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_36_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_priority_hist]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 1
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_36_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_priority_hist];
PRINT N'INDEX IX_AIDBA_36_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_priority_hist] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_priority_hist] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_priority_hist] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_36_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_priority_hist];
CREATE NONCLUSTERED INDEX IX_AIDBA_36_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_priority_hist] (
[ticket_id]
);

PRINT N'INDEX IX_AIDBA_36_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_priority_hist] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_36_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_37_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_37_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback] (
[item_category],[log_to],[rec_dt],[repair_by],[spare_status]
) INCLUDE (
[item_cw],[item_fault],[item_name],[item_part_no],[item_ship_code],[item_sn],[part_status],[repair_remark],[return_to],[spb_id],[ticket_id]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_37_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_spare_bback]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_37_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_spare_bback]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 16
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_37_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback];
PRINT N'INDEX IX_AIDBA_37_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_37_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback];
CREATE NONCLUSTERED INDEX IX_AIDBA_37_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback] (
[item_category],[log_to],[rec_dt],[repair_by],[spare_status]
) INCLUDE (
[item_cw],[item_fault],[item_name],[item_part_no],[item_ship_code],[item_sn],[part_status],[repair_remark],[return_to],[spb_id],[ticket_id]) ;

PRINT N'INDEX IX_AIDBA_37_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_37_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_38_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_38_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback_det] (
[act_by],[repair_status],[start_dt]
) INCLUDE (
[end_dt],[spb_id],[ticket_id]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_38_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_spare_bback_det]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_38_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_spare_bback_det]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 6
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_38_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback_det];
PRINT N'INDEX IX_AIDBA_38_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback_det] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback_det] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback_det] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_38_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback_det];
CREATE NONCLUSTERED INDEX IX_AIDBA_38_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback_det] (
[act_by],[repair_status],[start_dt]
) INCLUDE (
[end_dt],[spb_id],[ticket_id]) ;

PRINT N'INDEX IX_AIDBA_38_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_bback_det] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_38_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_39_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_39_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_spare_consign] (
[crdate],[spare_type]
) INCLUDE (
[csign_notes],[sp_id],[ticket_id]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_39_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_spare_consign]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_39_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_spare_consign]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 5
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_39_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_consign];
PRINT N'INDEX IX_AIDBA_39_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_consign] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_spare_consign] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_spare_consign] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_39_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_consign];
CREATE NONCLUSTERED INDEX IX_AIDBA_39_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_spare_consign] (
[crdate],[spare_type]
) INCLUDE (
[csign_notes],[sp_id],[ticket_id]) ;

PRINT N'INDEX IX_AIDBA_39_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_consign] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_39_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_40_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_40_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req] (
[req_by],[req_dt],[si_return],[spare_status],[spr_id],[ticket_id]
) INCLUDE (
[assign_type],[cibb_id],[col_by],[crby],[crdate],[email_flag],[issue_by],[issue_dt],[issue_item],[last_upd],[loan_flag],[notify_dt],[order_eta],[order_po_no],[print_flag],[sc_category],[sc_item_name],[sc_item_sn],[sc_item_to],[sc_part_no],[sc_remarks],[si_category],[si_item_from],[si_item_name],[si_item_sn],[si_part_eta],[si_part_no],[si_qty],[si_remarks],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_40_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_spare_req]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_40_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_spare_req]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 36
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_40_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req];
PRINT N'INDEX IX_AIDBA_40_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_40_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req];
CREATE NONCLUSTERED INDEX IX_AIDBA_40_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req] (
[req_by],[req_dt],[si_return],[spare_status],[spr_id],[ticket_id]
) INCLUDE (
[assign_type],[cibb_id],[col_by],[crby],[crdate],[email_flag],[issue_by],[issue_dt],[issue_item],[last_upd],[loan_flag],[notify_dt],[order_eta],[order_po_no],[print_flag],[sc_category],[sc_item_name],[sc_item_sn],[sc_item_to],[sc_part_no],[sc_remarks],[si_category],[si_item_from],[si_item_name],[si_item_sn],[si_part_eta],[si_part_no],[si_qty],[si_remarks],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_40_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_40_20190129 in the database.';

USE [intraCSSG];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_41_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_41_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req_hist] (
[si_item_from],[spr_id],[ticket_id]
) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_41_20190129' AND i.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_spare_req_hist]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_41_20190129' AND i2.object_id = OBJECT_ID('[intraCSSG].[dbo].[fs_trx_ticket_spare_req_hist]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 3
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_41_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req_hist];
PRINT N'INDEX IX_AIDBA_41_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req_hist] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req_hist] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req_hist] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_41_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req_hist];
CREATE NONCLUSTERED INDEX IX_AIDBA_41_20190129
ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req_hist] (
[si_item_from],[spr_id],[ticket_id]
);

PRINT N'INDEX IX_AIDBA_41_20190129 ON [intraCSSG].[dbo].[fs_trx_ticket_spare_req_hist] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_41_20190129 in the database.';

USE [intraHR];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_42_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_42_20190129
ON [intraHR].[dbo].[trn_trx_req] (
[staff_id]
) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_42_20190129' AND i.object_id = OBJECT_ID('[intraHR].[dbo].[trn_trx_req]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_42_20190129' AND i2.object_id = OBJECT_ID('[intraHR].[dbo].[trn_trx_req]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 1
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_42_20190129 ON [intraHR].[dbo].[trn_trx_req];
PRINT N'INDEX IX_AIDBA_42_20190129 ON [intraHR].[dbo].[trn_trx_req] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraHR].[dbo].[trn_trx_req] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraHR].[dbo].[trn_trx_req] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_42_20190129 ON [intraHR].[dbo].[trn_trx_req];
CREATE NONCLUSTERED INDEX IX_AIDBA_42_20190129
ON [intraHR].[dbo].[trn_trx_req] (
[staff_id]
);

PRINT N'INDEX IX_AIDBA_42_20190129 ON [intraHR].[dbo].[trn_trx_req] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_42_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_43_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_43_20190129
ON [intraMain].[dbo].[hrms_hrf] (
[hrf_delete],[hrf_id],[hrf_superior]
) INCLUDE (
[hrf_approval_hod],[hrf_approval_hr],[hrf_approval_md],[hrf_approval_superior],[hrf_costcenter],[hrf_hiringmanager],[hrf_hod],[hrf_hr],[hrf_joblayer],[hrf_md],[hrf_position],[hrf_requestdate],[hrf_serialno],[hrf_stage_canceldate],[hrf_stage_hireddate],[hrf_stage_identifieddate],[hrf_stage_kivdate],[hrf_stage_sourcingdate]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_43_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[hrms_hrf]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_43_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[hrms_hrf]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 21
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_43_20190129 ON [intraMain].[dbo].[hrms_hrf];
PRINT N'INDEX IX_AIDBA_43_20190129 ON [intraMain].[dbo].[hrms_hrf] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[hrms_hrf] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[hrms_hrf] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_43_20190129 ON [intraMain].[dbo].[hrms_hrf];
CREATE NONCLUSTERED INDEX IX_AIDBA_43_20190129
ON [intraMain].[dbo].[hrms_hrf] (
[hrf_delete],[hrf_id],[hrf_superior]
) INCLUDE (
[hrf_approval_hod],[hrf_approval_hr],[hrf_approval_md],[hrf_approval_superior],[hrf_costcenter],[hrf_hiringmanager],[hrf_hod],[hrf_hr],[hrf_joblayer],[hrf_md],[hrf_position],[hrf_requestdate],[hrf_serialno],[hrf_stage_canceldate],[hrf_stage_hireddate],[hrf_stage_identifieddate],[hrf_stage_kivdate],[hrf_stage_sourcingdate]) ;

PRINT N'INDEX IX_AIDBA_43_20190129 ON [intraMain].[dbo].[hrms_hrf] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_43_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_44_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_44_20190129
ON [intraMain].[dbo].[intranet_app_access] (
[access_id],[application_id],[login]
) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_44_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[intranet_app_access]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_44_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[intranet_app_access]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 3
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_44_20190129 ON [intraMain].[dbo].[intranet_app_access];
PRINT N'INDEX IX_AIDBA_44_20190129 ON [intraMain].[dbo].[intranet_app_access] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[intranet_app_access] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[intranet_app_access] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_44_20190129 ON [intraMain].[dbo].[intranet_app_access];
CREATE NONCLUSTERED INDEX IX_AIDBA_44_20190129
ON [intraMain].[dbo].[intranet_app_access] (
[access_id],[application_id],[login]
);

PRINT N'INDEX IX_AIDBA_44_20190129 ON [intraMain].[dbo].[intranet_app_access] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_44_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_45_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_45_20190129
ON [intraMain].[dbo].[leave_trans] (
[approval_status],[leave_type],[login],[start_date]
) INCLUDE (
[end_date],[half_day],[no_of_days]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_45_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[leave_trans]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_45_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[leave_trans]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 7
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_45_20190129 ON [intraMain].[dbo].[leave_trans];
PRINT N'INDEX IX_AIDBA_45_20190129 ON [intraMain].[dbo].[leave_trans] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[leave_trans] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[leave_trans] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_45_20190129 ON [intraMain].[dbo].[leave_trans];
CREATE NONCLUSTERED INDEX IX_AIDBA_45_20190129
ON [intraMain].[dbo].[leave_trans] (
[approval_status],[leave_type],[login],[start_date]
) INCLUDE (
[end_date],[half_day],[no_of_days]) ;

PRINT N'INDEX IX_AIDBA_45_20190129 ON [intraMain].[dbo].[leave_trans] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_45_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_46_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_46_20190129
ON [intraMain].[dbo].[leave_trans_2018] (
[approval_status]
) INCLUDE (
[end_date],[half_day],[login],[no_of_days],[start_date]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_46_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[leave_trans_2018]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_46_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[leave_trans_2018]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 6
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_46_20190129 ON [intraMain].[dbo].[leave_trans_2018];
PRINT N'INDEX IX_AIDBA_46_20190129 ON [intraMain].[dbo].[leave_trans_2018] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[leave_trans_2018] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[leave_trans_2018] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_46_20190129 ON [intraMain].[dbo].[leave_trans_2018];
CREATE NONCLUSTERED INDEX IX_AIDBA_46_20190129
ON [intraMain].[dbo].[leave_trans_2018] (
[approval_status]
) INCLUDE (
[end_date],[half_day],[login],[no_of_days],[start_date]) ;

PRINT N'INDEX IX_AIDBA_46_20190129 ON [intraMain].[dbo].[leave_trans_2018] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_46_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_47_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_47_20190129
ON [intraMain].[dbo].[login_pool] (
[login]
) INCLUDE (
[remote_host],[session_id],[time_login],[user_agent]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_47_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[login_pool]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_47_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[login_pool]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 5
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_47_20190129 ON [intraMain].[dbo].[login_pool];
PRINT N'INDEX IX_AIDBA_47_20190129 ON [intraMain].[dbo].[login_pool] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[login_pool] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[login_pool] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_47_20190129 ON [intraMain].[dbo].[login_pool];
CREATE NONCLUSTERED INDEX IX_AIDBA_47_20190129
ON [intraMain].[dbo].[login_pool] (
[login]
) INCLUDE (
[remote_host],[session_id],[time_login],[user_agent]) ;

PRINT N'INDEX IX_AIDBA_47_20190129 ON [intraMain].[dbo].[login_pool] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_47_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_48_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_48_20190129
ON [intraMain].[dbo].[personal_calendar] (
[pc_leave_id]
) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_48_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[personal_calendar]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_48_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[personal_calendar]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 1
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_48_20190129 ON [intraMain].[dbo].[personal_calendar];
PRINT N'INDEX IX_AIDBA_48_20190129 ON [intraMain].[dbo].[personal_calendar] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[personal_calendar] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[personal_calendar] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_48_20190129 ON [intraMain].[dbo].[personal_calendar];
CREATE NONCLUSTERED INDEX IX_AIDBA_48_20190129
ON [intraMain].[dbo].[personal_calendar] (
[pc_leave_id]
);

PRINT N'INDEX IX_AIDBA_48_20190129 ON [intraMain].[dbo].[personal_calendar] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_48_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_49_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_49_20190129
ON [intraMain].[dbo].[rbm_bookequip_trans] (
[rbm_event_id]
) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_49_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[rbm_bookequip_trans]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_49_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[rbm_bookequip_trans]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 1
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_49_20190129 ON [intraMain].[dbo].[rbm_bookequip_trans];
PRINT N'INDEX IX_AIDBA_49_20190129 ON [intraMain].[dbo].[rbm_bookequip_trans] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[rbm_bookequip_trans] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[rbm_bookequip_trans] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_49_20190129 ON [intraMain].[dbo].[rbm_bookequip_trans];
CREATE NONCLUSTERED INDEX IX_AIDBA_49_20190129
ON [intraMain].[dbo].[rbm_bookequip_trans] (
[rbm_event_id]
);

PRINT N'INDEX IX_AIDBA_49_20190129 ON [intraMain].[dbo].[rbm_bookequip_trans] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_49_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_50_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_50_20190129
ON [intraMain].[dbo].[rbm_bookroom_trans] (
[rbm_bookroom_id],[rbm_event_id],[rbm_event_start_date]
) INCLUDE (
[rbm_event_start_time]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_50_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[rbm_bookroom_trans]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_50_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[rbm_bookroom_trans]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 4
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_50_20190129 ON [intraMain].[dbo].[rbm_bookroom_trans];
PRINT N'INDEX IX_AIDBA_50_20190129 ON [intraMain].[dbo].[rbm_bookroom_trans] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[rbm_bookroom_trans] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[rbm_bookroom_trans] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_50_20190129 ON [intraMain].[dbo].[rbm_bookroom_trans];
CREATE NONCLUSTERED INDEX IX_AIDBA_50_20190129
ON [intraMain].[dbo].[rbm_bookroom_trans] (
[rbm_bookroom_id],[rbm_event_id],[rbm_event_start_date]
) INCLUDE (
[rbm_event_start_time]) ;

PRINT N'INDEX IX_AIDBA_50_20190129 ON [intraMain].[dbo].[rbm_bookroom_trans] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_50_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_51_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_51_20190129
ON [intraMain].[dbo].[rbm_event_trans] (
[rbm_event_start_date],[rbm_event_user]
) INCLUDE (
[rbm_event_id],[rbm_event_name],[rbm_event_organiser]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_51_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[rbm_event_trans]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_51_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[rbm_event_trans]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 5
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_51_20190129 ON [intraMain].[dbo].[rbm_event_trans];
PRINT N'INDEX IX_AIDBA_51_20190129 ON [intraMain].[dbo].[rbm_event_trans] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[rbm_event_trans] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[rbm_event_trans] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_51_20190129 ON [intraMain].[dbo].[rbm_event_trans];
CREATE NONCLUSTERED INDEX IX_AIDBA_51_20190129
ON [intraMain].[dbo].[rbm_event_trans] (
[rbm_event_start_date],[rbm_event_user]
) INCLUDE (
[rbm_event_id],[rbm_event_name],[rbm_event_organiser]) ;

PRINT N'INDEX IX_AIDBA_51_20190129 ON [intraMain].[dbo].[rbm_event_trans] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_51_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_52_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_52_20190129
ON [intraMain].[dbo].[rbm_refreshment_trans] (
[rbm_event_id]
) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_52_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[rbm_refreshment_trans]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_52_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[rbm_refreshment_trans]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 1
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_52_20190129 ON [intraMain].[dbo].[rbm_refreshment_trans];
PRINT N'INDEX IX_AIDBA_52_20190129 ON [intraMain].[dbo].[rbm_refreshment_trans] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[rbm_refreshment_trans] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[rbm_refreshment_trans] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_52_20190129 ON [intraMain].[dbo].[rbm_refreshment_trans];
CREATE NONCLUSTERED INDEX IX_AIDBA_52_20190129
ON [intraMain].[dbo].[rbm_refreshment_trans] (
[rbm_event_id]
);

PRINT N'INDEX IX_AIDBA_52_20190129 ON [intraMain].[dbo].[rbm_refreshment_trans] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_52_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_53_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_53_20190129
ON [intraMain].[dbo].[staff_master] (
[company],[department_id],[division_id],[group_id],[login],[sap_staff_no],[staff_gdd],[staff_type],[status],[superior]
) INCLUDE (
[alt_email],[contact_no],[contact_person],[contract_end_date],[contractor_agency],[crby],[csa_staff_no],[csam_id],[csc_id],[cust_account],[date_created],[date_joined],[date_terminated],[designation],[dob],[education],[effective_date],[email],[extension],[first_name],[handphone],[hod_id],[hr_partner],[image_path],[inscope],[job_group],[job_layer],[job_level],[job_sub_group],[job_sub_level],[last_name],[last_upd],[los_code],[marital],[name],[nationality],[new_ic],[nickname],[office_loc],[office_state],[old_ic],[phone],[pl_code],[pl_subsec],[race],[relation],[religion],[remark],[res_address],[res_phone],[salary_range],[sap_biz_unit],[sex],[sp_group_id],[terminated_by],[terminated_dt],[termination_remark],[upd_by],[wbse_code]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_53_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[staff_master]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_53_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[staff_master]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 69
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_53_20190129 ON [intraMain].[dbo].[staff_master];
PRINT N'INDEX IX_AIDBA_53_20190129 ON [intraMain].[dbo].[staff_master] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[staff_master] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[staff_master] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_53_20190129 ON [intraMain].[dbo].[staff_master];
CREATE NONCLUSTERED INDEX IX_AIDBA_53_20190129
ON [intraMain].[dbo].[staff_master] (
[company],[department_id],[division_id],[group_id],[login],[sap_staff_no],[staff_gdd],[staff_type],[status],[superior]
) INCLUDE (
[alt_email],[contact_no],[contact_person],[contract_end_date],[contractor_agency],[crby],[csa_staff_no],[csam_id],[csc_id],[cust_account],[date_created],[date_joined],[date_terminated],[designation],[dob],[education],[effective_date],[email],[extension],[first_name],[handphone],[hod_id],[hr_partner],[image_path],[inscope],[job_group],[job_layer],[job_level],[job_sub_group],[job_sub_level],[last_name],[last_upd],[los_code],[marital],[name],[nationality],[new_ic],[nickname],[office_loc],[office_state],[old_ic],[phone],[pl_code],[pl_subsec],[race],[relation],[religion],[remark],[res_address],[res_phone],[salary_range],[sap_biz_unit],[sex],[sp_group_id],[terminated_by],[terminated_dt],[termination_remark],[upd_by],[wbse_code]) ;

PRINT N'INDEX IX_AIDBA_53_20190129 ON [intraMain].[dbo].[staff_master] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_53_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_54_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_54_20190129
ON [intraMain].[dbo].[wca_claim_hdr] (
[approve_staff],[claim_year],[grand_total],[login],[status],[upload_date],[upload_flag]
) INCLUDE (
[approved_date],[claim_id],[claim_month],[staff_gdd],[total_allow],[total_flexi],[total_overtime],[total_reimburse],[total_transport],[total_travel]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_54_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[wca_claim_hdr]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_54_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[wca_claim_hdr]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 17
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_54_20190129 ON [intraMain].[dbo].[wca_claim_hdr];
PRINT N'INDEX IX_AIDBA_54_20190129 ON [intraMain].[dbo].[wca_claim_hdr] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[wca_claim_hdr] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[wca_claim_hdr] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_54_20190129 ON [intraMain].[dbo].[wca_claim_hdr];
CREATE NONCLUSTERED INDEX IX_AIDBA_54_20190129
ON [intraMain].[dbo].[wca_claim_hdr] (
[approve_staff],[claim_year],[grand_total],[login],[status],[upload_date],[upload_flag]
) INCLUDE (
[approved_date],[claim_id],[claim_month],[staff_gdd],[total_allow],[total_flexi],[total_overtime],[total_reimburse],[total_transport],[total_travel]) ;

PRINT N'INDEX IX_AIDBA_54_20190129 ON [intraMain].[dbo].[wca_claim_hdr] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_54_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_55_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_55_20190129
ON [intraMain].[dbo].[wca_flexi_claim] (
[fin_year],[flexi_acc],[user_id]
) INCLUDE (
[claim_id],[flexi_ac_code],[flexi_amt],[flexi_pl_code],[flexi_tax_code]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_55_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[wca_flexi_claim]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_55_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[wca_flexi_claim]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 8
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_55_20190129 ON [intraMain].[dbo].[wca_flexi_claim];
PRINT N'INDEX IX_AIDBA_55_20190129 ON [intraMain].[dbo].[wca_flexi_claim] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[wca_flexi_claim] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[wca_flexi_claim] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_55_20190129 ON [intraMain].[dbo].[wca_flexi_claim];
CREATE NONCLUSTERED INDEX IX_AIDBA_55_20190129
ON [intraMain].[dbo].[wca_flexi_claim] (
[fin_year],[flexi_acc],[user_id]
) INCLUDE (
[claim_id],[flexi_ac_code],[flexi_amt],[flexi_pl_code],[flexi_tax_code]) ;

PRINT N'INDEX IX_AIDBA_55_20190129 ON [intraMain].[dbo].[wca_flexi_claim] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_55_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_56_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_56_20190129
ON [intraMain].[dbo].[wsr_item_mast] (
[status],[wsr_group_id]
) INCLUDE (
[wsr_avail_qty],[wsr_booked_qty],[wsr_delv_uom_id],[wsr_delv_uom_qty],[wsr_item_desc],[wsr_item_id],[wsr_item_img],[wsr_item_min_qty],[wsr_item_name],[wsr_item_ord_level],[wsr_item_remark],[wsr_item_std_cost]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_56_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[wsr_item_mast]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_56_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[wsr_item_mast]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 14
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_56_20190129 ON [intraMain].[dbo].[wsr_item_mast];
PRINT N'INDEX IX_AIDBA_56_20190129 ON [intraMain].[dbo].[wsr_item_mast] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[wsr_item_mast] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[wsr_item_mast] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_56_20190129 ON [intraMain].[dbo].[wsr_item_mast];
CREATE NONCLUSTERED INDEX IX_AIDBA_56_20190129
ON [intraMain].[dbo].[wsr_item_mast] (
[status],[wsr_group_id]
) INCLUDE (
[wsr_avail_qty],[wsr_booked_qty],[wsr_delv_uom_id],[wsr_delv_uom_qty],[wsr_item_desc],[wsr_item_id],[wsr_item_img],[wsr_item_min_qty],[wsr_item_name],[wsr_item_ord_level],[wsr_item_remark],[wsr_item_std_cost]) ;

PRINT N'INDEX IX_AIDBA_56_20190129 ON [intraMain].[dbo].[wsr_item_mast] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_56_20190129 in the database.';

USE [intraMain];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_57_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_57_20190129
ON [intraMain].[dbo].[wsr_stationery_req_hdr] (
[wsr_approve_staff],[wsr_req_date]
) INCLUDE (
[wsr_request_id],[wsr_request_staff]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_57_20190129' AND i.object_id = OBJECT_ID('[intraMain].[dbo].[wsr_stationery_req_hdr]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_57_20190129' AND i2.object_id = OBJECT_ID('[intraMain].[dbo].[wsr_stationery_req_hdr]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 4
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_57_20190129 ON [intraMain].[dbo].[wsr_stationery_req_hdr];
PRINT N'INDEX IX_AIDBA_57_20190129 ON [intraMain].[dbo].[wsr_stationery_req_hdr] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraMain].[dbo].[wsr_stationery_req_hdr] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraMain].[dbo].[wsr_stationery_req_hdr] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_57_20190129 ON [intraMain].[dbo].[wsr_stationery_req_hdr];
CREATE NONCLUSTERED INDEX IX_AIDBA_57_20190129
ON [intraMain].[dbo].[wsr_stationery_req_hdr] (
[wsr_approve_staff],[wsr_req_date]
) INCLUDE (
[wsr_request_id],[wsr_request_staff]) ;

PRINT N'INDEX IX_AIDBA_57_20190129 ON [intraMain].[dbo].[wsr_stationery_req_hdr] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_57_20190129 in the database.';

USE [intraSAP];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_58_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_58_20190129
ON [intraSAP].[dbo].[interface_invoice_master] (
[interface_dt],[interface_status],[mis_date_doc],[sap_currency_code],[sap_gl_code],[sap_orgcode],[sap_payment_term],[sap_pl_code],[sap_sr_no]
) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_58_20190129' AND i.object_id = OBJECT_ID('[intraSAP].[dbo].[interface_invoice_master]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_58_20190129' AND i2.object_id = OBJECT_ID('[intraSAP].[dbo].[interface_invoice_master]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 9
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_58_20190129 ON [intraSAP].[dbo].[interface_invoice_master];
PRINT N'INDEX IX_AIDBA_58_20190129 ON [intraSAP].[dbo].[interface_invoice_master] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraSAP].[dbo].[interface_invoice_master] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraSAP].[dbo].[interface_invoice_master] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_58_20190129 ON [intraSAP].[dbo].[interface_invoice_master];
CREATE NONCLUSTERED INDEX IX_AIDBA_58_20190129
ON [intraSAP].[dbo].[interface_invoice_master] (
[interface_dt],[interface_status],[mis_date_doc],[sap_currency_code],[sap_gl_code],[sap_orgcode],[sap_payment_term],[sap_pl_code],[sap_sr_no]
);

PRINT N'INDEX IX_AIDBA_58_20190129 ON [intraSAP].[dbo].[interface_invoice_master] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_58_20190129 in the database.';

USE [intraSAP];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_59_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_59_20190129
ON [intraSAP].[dbo].[sap_ar] (
[clearing_date],[clearing_no],[cust_no],[dc_ind],[doc_no],[doc_type],[ref_no],[reverse_doc_fyear],[reverse_doc_no]
) INCLUDE (
[doc_date]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_59_20190129' AND i.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_ar]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_59_20190129' AND i2.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_ar]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 10
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_59_20190129 ON [intraSAP].[dbo].[sap_ar];
PRINT N'INDEX IX_AIDBA_59_20190129 ON [intraSAP].[dbo].[sap_ar] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraSAP].[dbo].[sap_ar] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraSAP].[dbo].[sap_ar] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_59_20190129 ON [intraSAP].[dbo].[sap_ar];
CREATE NONCLUSTERED INDEX IX_AIDBA_59_20190129
ON [intraSAP].[dbo].[sap_ar] (
[clearing_date],[clearing_no],[cust_no],[dc_ind],[doc_no],[doc_type],[ref_no],[reverse_doc_fyear],[reverse_doc_no]
) INCLUDE (
[doc_date]) ;

PRINT N'INDEX IX_AIDBA_59_20190129 ON [intraSAP].[dbo].[sap_ar] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_59_20190129 in the database.';

USE [intraSAP];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_60_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_60_20190129
ON [intraSAP].[dbo].[sap_customer_master] (
[abc_class],[change_dt],[city],[crdate],[credit_limit],[cust_acct_grp],[orgcode],[party_ref],[risk_category],[roc_no],[search_term],[search_term2],[trx_dt]
) INCLUDE (
[country],[cust_name1],[cust_name2],[cust_no],[postcode],[region],[street],[street4],[street5]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_60_20190129' AND i.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_customer_master]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_60_20190129' AND i2.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_customer_master]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 22
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_60_20190129 ON [intraSAP].[dbo].[sap_customer_master];
PRINT N'INDEX IX_AIDBA_60_20190129 ON [intraSAP].[dbo].[sap_customer_master] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraSAP].[dbo].[sap_customer_master] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraSAP].[dbo].[sap_customer_master] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_60_20190129 ON [intraSAP].[dbo].[sap_customer_master];
CREATE NONCLUSTERED INDEX IX_AIDBA_60_20190129
ON [intraSAP].[dbo].[sap_customer_master] (
[abc_class],[change_dt],[city],[crdate],[credit_limit],[cust_acct_grp],[orgcode],[party_ref],[risk_category],[roc_no],[search_term],[search_term2],[trx_dt]
) INCLUDE (
[country],[cust_name1],[cust_name2],[cust_no],[postcode],[region],[street],[street4],[street5]) ;

PRINT N'INDEX IX_AIDBA_60_20190129 ON [intraSAP].[dbo].[sap_customer_master] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_60_20190129 in the database.';

USE [intraSAP];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_61_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_61_20190129
ON [intraSAP].[dbo].[sap_do] (
[batch_no],[do_date_proof],[do_goods_issue_date],[do_no],[orgcode],[so_item],[so_no],[trx_dt]
) INCLUDE (
[billing_status],[deliver_qty],[do_date],[do_item],[external_delivery],[item_cost],[item_net_sales_value],[material_desc],[material_no],[notes_to_cust],[pick_qty],[product_hrachy_desc],[shipto_city],[shipto_country],[shipto_cust_name1],[shipto_cust_name2],[shipto_cust_no],[shipto_postcode],[shipto_state],[shipto_street],[shipto_street4],[shipto_street5],[storage_loc],[wbs_element]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_61_20190129' AND i.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_do]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_61_20190129' AND i2.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_do]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 32
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_61_20190129 ON [intraSAP].[dbo].[sap_do];
PRINT N'INDEX IX_AIDBA_61_20190129 ON [intraSAP].[dbo].[sap_do] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraSAP].[dbo].[sap_do] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraSAP].[dbo].[sap_do] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_61_20190129 ON [intraSAP].[dbo].[sap_do];
CREATE NONCLUSTERED INDEX IX_AIDBA_61_20190129
ON [intraSAP].[dbo].[sap_do] (
[batch_no],[do_date_proof],[do_goods_issue_date],[do_no],[orgcode],[so_item],[so_no],[trx_dt]
) INCLUDE (
[billing_status],[deliver_qty],[do_date],[do_item],[external_delivery],[item_cost],[item_net_sales_value],[material_desc],[material_no],[notes_to_cust],[pick_qty],[product_hrachy_desc],[shipto_city],[shipto_country],[shipto_cust_name1],[shipto_cust_name2],[shipto_cust_no],[shipto_postcode],[shipto_state],[shipto_street],[shipto_street4],[shipto_street5],[storage_loc],[wbs_element]) ;

PRINT N'INDEX IX_AIDBA_61_20190129 ON [intraSAP].[dbo].[sap_do] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_61_20190129 in the database.';

USE [intraSAP];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_62_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_62_20190129
ON [intraSAP].[dbo].[sap_do_sn] (
[crdate],[do_no],[serial_no],[trx_dt]
) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_62_20190129' AND i.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_do_sn]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_62_20190129' AND i2.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_do_sn]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 4
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_62_20190129 ON [intraSAP].[dbo].[sap_do_sn];
PRINT N'INDEX IX_AIDBA_62_20190129 ON [intraSAP].[dbo].[sap_do_sn] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraSAP].[dbo].[sap_do_sn] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraSAP].[dbo].[sap_do_sn] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_62_20190129 ON [intraSAP].[dbo].[sap_do_sn];
CREATE NONCLUSTERED INDEX IX_AIDBA_62_20190129
ON [intraSAP].[dbo].[sap_do_sn] (
[crdate],[do_no],[serial_no],[trx_dt]
);

PRINT N'INDEX IX_AIDBA_62_20190129 ON [intraSAP].[dbo].[sap_do_sn] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_62_20190129 in the database.';

USE [intraSAP];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_63_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_63_20190129
ON [intraSAP].[dbo].[sap_inv] (
[cancel_status],[do_item],[do_no],[inv_type],[orgcode],[product_category],[sales_office],[sr_no]
) INCLUDE (
[billto_city],[billto_country],[billto_cust_name1],[billto_cust_name2],[billto_postcode],[billto_state],[billto_street],[billto_street4],[billto_street5],[exchange_rate],[external_inv_no],[inv_bill_date],[inv_no],[inv_ref],[net_value],[ref_item],[sales_coord_no],[soldto_cust_name1],[soldto_cust_name2],[soldto_cust_no],[sr_name]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_63_20190129' AND i.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_inv]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_63_20190129' AND i2.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_inv]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 29
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_63_20190129 ON [intraSAP].[dbo].[sap_inv];
PRINT N'INDEX IX_AIDBA_63_20190129 ON [intraSAP].[dbo].[sap_inv] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraSAP].[dbo].[sap_inv] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraSAP].[dbo].[sap_inv] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_63_20190129 ON [intraSAP].[dbo].[sap_inv];
CREATE NONCLUSTERED INDEX IX_AIDBA_63_20190129
ON [intraSAP].[dbo].[sap_inv] (
[cancel_status],[do_item],[do_no],[inv_type],[orgcode],[product_category],[sales_office],[sr_no]
) INCLUDE (
[billto_city],[billto_country],[billto_cust_name1],[billto_cust_name2],[billto_postcode],[billto_state],[billto_street],[billto_street4],[billto_street5],[exchange_rate],[external_inv_no],[inv_bill_date],[inv_no],[inv_ref],[net_value],[ref_item],[sales_coord_no],[soldto_cust_name1],[soldto_cust_name2],[soldto_cust_no],[sr_name]) ;

PRINT N'INDEX IX_AIDBA_63_20190129 ON [intraSAP].[dbo].[sap_inv] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_63_20190129 in the database.';

USE [intraSAP];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_64_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_64_20190129
ON [intraSAP].[dbo].[sap_material_master] (
[product_hrachy]
) INCLUDE (
[material_no]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_64_20190129' AND i.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_material_master]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_64_20190129' AND i2.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_material_master]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 2
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_64_20190129 ON [intraSAP].[dbo].[sap_material_master];
PRINT N'INDEX IX_AIDBA_64_20190129 ON [intraSAP].[dbo].[sap_material_master] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraSAP].[dbo].[sap_material_master] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraSAP].[dbo].[sap_material_master] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_64_20190129 ON [intraSAP].[dbo].[sap_material_master];
CREATE NONCLUSTERED INDEX IX_AIDBA_64_20190129
ON [intraSAP].[dbo].[sap_material_master] (
[product_hrachy]
) INCLUDE (
[material_no]) ;

PRINT N'INDEX IX_AIDBA_64_20190129 ON [intraSAP].[dbo].[sap_material_master] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_64_20190129 in the database.';

USE [intraSAP];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_65_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_65_20190129
ON [intraSAP].[dbo].[sap_po] (
[de_complete_ind],[doc_type],[po_crdate],[po_creator],[po_doc_date],[po_item_del_flag],[po_item_return_flag],[po_no],[po_org],[product_category],[product_principal],[release_status],[req_sr_no],[so_item],[so_no],[trx_dt],[vendor_country],[vendor_name]
) INCLUDE (
[cost_center],[delivery_text],[exchange_rate],[gl_code],[info_rec_note],[info_rec_po_text],[item_net_price],[item_qty],[mat_po_text],[material_desc],[material_group_desc],[material_no],[pl_code],[po_currency],[po_item],[po_item_text],[po_text],[price_cond_perc],[req_de_date],[req_sr_name],[wbs_element]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_65_20190129' AND i.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_po]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_65_20190129' AND i2.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_po]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 39
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_65_20190129 ON [intraSAP].[dbo].[sap_po];
PRINT N'INDEX IX_AIDBA_65_20190129 ON [intraSAP].[dbo].[sap_po] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraSAP].[dbo].[sap_po] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraSAP].[dbo].[sap_po] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_65_20190129 ON [intraSAP].[dbo].[sap_po];
CREATE NONCLUSTERED INDEX IX_AIDBA_65_20190129
ON [intraSAP].[dbo].[sap_po] (
[de_complete_ind],[doc_type],[po_crdate],[po_creator],[po_doc_date],[po_item_del_flag],[po_item_return_flag],[po_no],[po_org],[product_category],[product_principal],[release_status],[req_sr_no],[so_item],[so_no],[trx_dt],[vendor_country],[vendor_name]
) INCLUDE (
[cost_center],[delivery_text],[exchange_rate],[gl_code],[info_rec_note],[info_rec_po_text],[item_net_price],[item_qty],[mat_po_text],[material_desc],[material_group_desc],[material_no],[pl_code],[po_currency],[po_item],[po_item_text],[po_text],[price_cond_perc],[req_de_date],[req_sr_name],[wbs_element]) ;

PRINT N'INDEX IX_AIDBA_65_20190129 ON [intraSAP].[dbo].[sap_po] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_65_20190129 in the database.';

USE [intraSAP];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_66_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_66_20190129
ON [intraSAP].[dbo].[sap_po_hist] (
[entry_date],[move_type],[po_item],[po_no],[trx_type]
) INCLUDE (
[local_amt],[qty],[ref_no]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_66_20190129' AND i.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_po_hist]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_66_20190129' AND i2.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_po_hist]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 8
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_66_20190129 ON [intraSAP].[dbo].[sap_po_hist];
PRINT N'INDEX IX_AIDBA_66_20190129 ON [intraSAP].[dbo].[sap_po_hist] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraSAP].[dbo].[sap_po_hist] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraSAP].[dbo].[sap_po_hist] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_66_20190129 ON [intraSAP].[dbo].[sap_po_hist];
CREATE NONCLUSTERED INDEX IX_AIDBA_66_20190129
ON [intraSAP].[dbo].[sap_po_hist] (
[entry_date],[move_type],[po_item],[po_no],[trx_type]
) INCLUDE (
[local_amt],[qty],[ref_no]) ;

PRINT N'INDEX IX_AIDBA_66_20190129 ON [intraSAP].[dbo].[sap_po_hist] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_66_20190129 in the database.';

USE [intraSAP];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_67_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_67_20190129
ON [intraSAP].[dbo].[sap_product_hrachy_master] (
[tower_id]
) INCLUDE (
[hrachy_level],[product_hrachy]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_67_20190129' AND i.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_product_hrachy_master]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_67_20190129' AND i2.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_product_hrachy_master]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 3
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_67_20190129 ON [intraSAP].[dbo].[sap_product_hrachy_master];
PRINT N'INDEX IX_AIDBA_67_20190129 ON [intraSAP].[dbo].[sap_product_hrachy_master] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraSAP].[dbo].[sap_product_hrachy_master] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraSAP].[dbo].[sap_product_hrachy_master] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_67_20190129 ON [intraSAP].[dbo].[sap_product_hrachy_master];
CREATE NONCLUSTERED INDEX IX_AIDBA_67_20190129
ON [intraSAP].[dbo].[sap_product_hrachy_master] (
[tower_id]
) INCLUDE (
[hrachy_level],[product_hrachy]) ;

PRINT N'INDEX IX_AIDBA_67_20190129 ON [intraSAP].[dbo].[sap_product_hrachy_master] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_67_20190129 in the database.';

USE [intraSAP];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_68_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_68_20190129
ON [intraSAP].[dbo].[sap_so] (
[cust_po_no],[delivery_block],[orgcode],[product_category],[product_principal],[reject_reason],[sales_coord_no],[sales_office],[so_date],[so_no],[so_type],[sr_no],[trx_dt]
) INCLUDE (
[dist_channel],[doc_currency],[exchange_rate],[invoicing_req],[item_category],[material_desc],[material_no],[material_sales_txt],[net_value],[pl_code],[req_delivery_date],[sales_coord_name],[sales_qty],[so_item],[so_type_desc],[soldto_cust_name1],[soldto_cust_name2],[soldto_cust_no],[sr_name],[tax_amt],[tax_code],[wbs_element],[wbs_no]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_68_20190129' AND i.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_so]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_68_20190129' AND i2.object_id = OBJECT_ID('[intraSAP].[dbo].[sap_so]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 36
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_68_20190129 ON [intraSAP].[dbo].[sap_so];
PRINT N'INDEX IX_AIDBA_68_20190129 ON [intraSAP].[dbo].[sap_so] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraSAP].[dbo].[sap_so] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraSAP].[dbo].[sap_so] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_68_20190129 ON [intraSAP].[dbo].[sap_so];
CREATE NONCLUSTERED INDEX IX_AIDBA_68_20190129
ON [intraSAP].[dbo].[sap_so] (
[cust_po_no],[delivery_block],[orgcode],[product_category],[product_principal],[reject_reason],[sales_coord_no],[sales_office],[so_date],[so_no],[so_type],[sr_no],[trx_dt]
) INCLUDE (
[dist_channel],[doc_currency],[exchange_rate],[invoicing_req],[item_category],[material_desc],[material_no],[material_sales_txt],[net_value],[pl_code],[req_delivery_date],[sales_coord_name],[sales_qty],[so_item],[so_type_desc],[soldto_cust_name1],[soldto_cust_name2],[soldto_cust_no],[sr_name],[tax_amt],[tax_code],[wbs_element],[wbs_no]) ;

PRINT N'INDEX IX_AIDBA_68_20190129 ON [intraSAP].[dbo].[sap_so] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_68_20190129 in the database.';

USE [intraTech];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_69_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_69_20190129
ON [intraTech].[dbo].[pstask_request_trans] (
[app_status],[req_by]
) INCLUDE (
[assigned_se],[event_datetime],[req_date],[rid],[target_datetime]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_69_20190129' AND i.object_id = OBJECT_ID('[intraTech].[dbo].[pstask_request_trans]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_69_20190129' AND i2.object_id = OBJECT_ID('[intraTech].[dbo].[pstask_request_trans]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 7
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_69_20190129 ON [intraTech].[dbo].[pstask_request_trans];
PRINT N'INDEX IX_AIDBA_69_20190129 ON [intraTech].[dbo].[pstask_request_trans] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraTech].[dbo].[pstask_request_trans] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraTech].[dbo].[pstask_request_trans] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_69_20190129 ON [intraTech].[dbo].[pstask_request_trans];
CREATE NONCLUSTERED INDEX IX_AIDBA_69_20190129
ON [intraTech].[dbo].[pstask_request_trans] (
[app_status],[req_by]
) INCLUDE (
[assigned_se],[event_datetime],[req_date],[rid],[target_datetime]) ;

PRINT N'INDEX IX_AIDBA_69_20190129 ON [intraTech].[dbo].[pstask_request_trans] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_69_20190129 in the database.';

USE [intraTech];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_70_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_70_20190129
ON [intraTech].[dbo].[ts_project_master] (
[crby],[prj_status]
) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_70_20190129' AND i.object_id = OBJECT_ID('[intraTech].[dbo].[ts_project_master]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_70_20190129' AND i2.object_id = OBJECT_ID('[intraTech].[dbo].[ts_project_master]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 2
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_70_20190129 ON [intraTech].[dbo].[ts_project_master];
PRINT N'INDEX IX_AIDBA_70_20190129 ON [intraTech].[dbo].[ts_project_master] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraTech].[dbo].[ts_project_master] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraTech].[dbo].[ts_project_master] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_70_20190129 ON [intraTech].[dbo].[ts_project_master];
CREATE NONCLUSTERED INDEX IX_AIDBA_70_20190129
ON [intraTech].[dbo].[ts_project_master] (
[crby],[prj_status]
);

PRINT N'INDEX IX_AIDBA_70_20190129 ON [intraTech].[dbo].[ts_project_master] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_70_20190129 in the database.';

USE [intraTech];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_71_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_71_20190129
ON [intraTech].[dbo].[ts_project_support_master] (
[support_id]
) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_71_20190129' AND i.object_id = OBJECT_ID('[intraTech].[dbo].[ts_project_support_master]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_71_20190129' AND i2.object_id = OBJECT_ID('[intraTech].[dbo].[ts_project_support_master]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 1
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_71_20190129 ON [intraTech].[dbo].[ts_project_support_master];
PRINT N'INDEX IX_AIDBA_71_20190129 ON [intraTech].[dbo].[ts_project_support_master] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraTech].[dbo].[ts_project_support_master] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraTech].[dbo].[ts_project_support_master] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_71_20190129 ON [intraTech].[dbo].[ts_project_support_master];
CREATE NONCLUSTERED INDEX IX_AIDBA_71_20190129
ON [intraTech].[dbo].[ts_project_support_master] (
[support_id]
);

PRINT N'INDEX IX_AIDBA_71_20190129 ON [intraTech].[dbo].[ts_project_support_master] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_71_20190129 in the database.';

USE [intraTech];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_72_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_72_20190129
ON [intraTech].[dbo].[ts_project_timesheet] (
[emp_id],[prj_id],[ts_date]
) INCLUDE (
[ts_category],[ts_desc],[ts_hour],[ts_subcat]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_72_20190129' AND i.object_id = OBJECT_ID('[intraTech].[dbo].[ts_project_timesheet]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_72_20190129' AND i2.object_id = OBJECT_ID('[intraTech].[dbo].[ts_project_timesheet]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 7
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_72_20190129 ON [intraTech].[dbo].[ts_project_timesheet];
PRINT N'INDEX IX_AIDBA_72_20190129 ON [intraTech].[dbo].[ts_project_timesheet] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraTech].[dbo].[ts_project_timesheet] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraTech].[dbo].[ts_project_timesheet] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_72_20190129 ON [intraTech].[dbo].[ts_project_timesheet];
CREATE NONCLUSTERED INDEX IX_AIDBA_72_20190129
ON [intraTech].[dbo].[ts_project_timesheet] (
[emp_id],[prj_id],[ts_date]
) INCLUDE (
[ts_category],[ts_desc],[ts_hour],[ts_subcat]) ;

PRINT N'INDEX IX_AIDBA_72_20190129 ON [intraTech].[dbo].[ts_project_timesheet] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_72_20190129 in the database.';

USE [intraTech];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_73_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_73_20190129
ON [intraTech].[dbo].[ts_project_timesheet_se] (
[emp_id],[prj_id],[support_id],[ts_date]
) INCLUDE (
[crby],[crdate],[last_upd],[ts_category],[ts_desc],[ts_etime],[ts_hour],[ts_id],[ts_onsite],[ts_stime],[ts_subcat],[upd_by]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_73_20190129' AND i.object_id = OBJECT_ID('[intraTech].[dbo].[ts_project_timesheet_se]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_73_20190129' AND i2.object_id = OBJECT_ID('[intraTech].[dbo].[ts_project_timesheet_se]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 16
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_73_20190129 ON [intraTech].[dbo].[ts_project_timesheet_se];
PRINT N'INDEX IX_AIDBA_73_20190129 ON [intraTech].[dbo].[ts_project_timesheet_se] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraTech].[dbo].[ts_project_timesheet_se] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraTech].[dbo].[ts_project_timesheet_se] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_73_20190129 ON [intraTech].[dbo].[ts_project_timesheet_se];
CREATE NONCLUSTERED INDEX IX_AIDBA_73_20190129
ON [intraTech].[dbo].[ts_project_timesheet_se] (
[emp_id],[prj_id],[support_id],[ts_date]
) INCLUDE (
[crby],[crdate],[last_upd],[ts_category],[ts_desc],[ts_etime],[ts_hour],[ts_id],[ts_onsite],[ts_stime],[ts_subcat],[upd_by]) ;

PRINT N'INDEX IX_AIDBA_73_20190129 ON [intraTech].[dbo].[ts_project_timesheet_se] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_73_20190129 in the database.';

USE [intraTech];
IF NOT EXISTS ( SELECT * FROM SYS.INDEXES WHERE NAME = N'IX_AIDBA_74_20190129')
BEGIN
CREATE NONCLUSTERED INDEX IX_AIDBA_74_20190129
ON [intraTech].[dbo].[ts_timesheet_misc] (
[emp_id],[ts_date]
) INCLUDE (
[ts_category],[ts_hour],[ts_subcat]) WITH STATISTICS_ONLY ;

SELECT
@iid = ic.object_id , @oid = ic.index_id , @is_disable = i.is_disabled, @iname = i.Name
FROM sys.indexes i
INNER JOIN sys.index_columns ic
ON i.index_id = ic.index_id AND i.object_id = ic.object_id
WHERE i.name <> N'IX_AIDBA_74_20190129' AND i.object_id = OBJECT_ID('[intraTech].[dbo].[ts_timesheet_misc]')
AND EXISTS
(SELECT *
FROM sys.indexes i2
INNER JOIN sys.index_columns ic2
ON i2.index_id = ic2.index_id AND i2.object_id = ic2.object_id
WHERE i2.name = N'IX_AIDBA_74_20190129' AND i2.object_id = OBJECT_ID('[intraTech].[dbo].[ts_timesheet_misc]')
AND i.object_id = i2.object_id
AND i.type = i2.type
AND ic.index_column_id = ic2.index_column_id
AND ic.column_id = ic2.column_id
AND ic.key_ordinal = ic2.key_ordinal
AND ic.partition_ordinal = ic2.partition_ordinal
AND ic.is_descending_key = ic2.is_descending_key
AND ic.is_included_column = ic2.is_included_column)
GROUP BY ic.object_id,ic.index_id, i.is_disabled, i.Name
HAVING
COUNT(*) = 5
AND COUNT(*) = (SELECT COUNT(*) FROM sys.index_columns ic3 WHERE ic3.index_id = ic.index_id AND ic3.object_id = ic.object_id);

IF @iid IS NOT NULL
BEGIN
DROP INDEX IX_AIDBA_74_20190129 ON [intraTech].[dbo].[ts_timesheet_misc];
PRINT N'INDEX IX_AIDBA_74_20190129 ON [intraTech].[dbo].[ts_timesheet_misc] IS DROPPED DUE TO DUPLICATION.';
IF @is_disable = 1
BEGIN
SET @Smt = N'ALTER INDEX '+@iname+' ON [intraTech].[dbo].[ts_timesheet_misc] REBUILD;';
EXEC (@Smt);
PRINT N'INDEX '+@iname+' ON [intraTech].[dbo].[ts_timesheet_misc] IS ENABLED.';
END
END
ELSE
BEGIN
DROP INDEX IX_AIDBA_74_20190129 ON [intraTech].[dbo].[ts_timesheet_misc];
CREATE NONCLUSTERED INDEX IX_AIDBA_74_20190129
ON [intraTech].[dbo].[ts_timesheet_misc] (
[emp_id],[ts_date]
) INCLUDE (
[ts_category],[ts_hour],[ts_subcat]) ;

PRINT N'INDEX IX_AIDBA_74_20190129 ON [intraTech].[dbo].[ts_timesheet_misc] IS CREATED.';
END
END
ELSE
PRINT N'There is already an index named IX_AIDBA_74_20190129 in the database.';