CHECK IF EXISTS STORE PROCEDURE, FUNCTION & INDEX
CHECK IF EXISTS STORE PROCEDURE, FUNCTION & INDEX
========== PROCEDURE ==========
IF EXISTS ( SELECT *
FROM sysobjects
WHERE id = OBJECT_ID(N"[dbo].[Billing_SearchFirstAndFinalList]")
AND OBJECTPROPERTY(id, N"IsProcedure") = 1 )
BEGIN
DROP PROCEDURE [dbo].[Billing_SearchFirstAndFinalList];
END;
GO
=========== FUNCTION ===========
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N"[dbo].[Billing_GetAccountTransactionDetail_fxn]")
AND type IN ( N"FN", N"IF", N"TF", N"FS", N"FT" ) )
BEGIN
DROP FUNCTION [dbo].[Billing_GetAccountTransactionDetail_fxn];
END;
GO
=========== INDEX ===========
IF NOT EXISTS ( SELECT 1
FROM sys.indexes I
INNER JOIN sys.tables T ON I.object_id = T.object_id
INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
WHERE I.name = "IX_Plan_ProjectItem_ProjectID" -- Index name
AND T.name = "Plan_ProjectItem" -- Table name
AND S.name = "dbo" ) --Schema Name
BEGIN
CREATE NONCLUSTERED INDEX [IX_Plan_ProjectItem_ProjectID]
ON [dbo].[Plan_ProjectItem] ([ProjectID])
INCLUDE ([PlanProjectItem_ID],[ProjectItemID],[SCOAItemID],[FinYear],[CapturerID],[DateCaptured],[ModifierID],[DateModified],[ProjectFundYearID],[SCOAFundId],[BudgetAmount],[BudgetAmountCurP1],[BudgetAmountCurP2],[SCOAFunctionId],[SCOARegionId],[DivisionId],[BudgetSplitID],[VirementId],[HistoricalProjectCode],[AdjustmentId],[ModificationNumber],[SCOACostingID],[IsItemLocked],[CreditDebit]);
END;
ELSE
BEGIN
SELECT 1 AS result;
END;
=========== COLUMN ADD ===========
IF NOT EXISTS(SELECT 1 FROM sys.columns
WHERE Name = N"DiscountControlScoaItemID"
AND Object_ID = Object_ID(N"Const_ServiceType"))
BEGIN
ALTER TABLE dbo.Const_ServiceType
ADD DiscountControlScoaItemID INT NULL
END
GO
=========== COLUMN REMOVE ===========
IF EXISTS(SELECT 1 FROM sys.columns
WHERE Name = N"discountRebatecontrolScoaItemID"
AND Object_ID = Object_ID(N"Const_TariffVote"))
BEGIN
ALTER TABLE dbo.Const_TariffVote
DROP column discountRebatecontrolScoaItemID
END
GO
