ALTER User Defined Table Type without removing dependancy

Use LotusBOS_Development
Go
If Object_Id('dbo.SPA_SystemUDTT_Update') Is Not Null
Begin
Drop Proc dbo.SPA_SystemUDTT_Update
End
Go
/*
Created By : Dhaval Chauhan, Created On : 24 Feb 2014
Exec SPA_SystemUDTT_Update
@ObjectName = 'TBLB_SupplierPLOFType',
@TableTypeScript = '
/****** Object:  UserDefinedTableType [dbo].[TBLB_SupplierPLOFType]    Script Date: 3/7/2014 5:35:23 PM ******/
CREATE TYPE [dbo].[TBLB_SupplierPLOFType] AS TABLE(
[SuPLItemType] [varchar](2) NULL,
[SuPLSalesType] [varchar](2) NULL,
[SuPLWeightType] [varchar](2) NULL,
[SuPLVATID] [tinyint] NULL,
[SuPLID] [bigint] NOT NULL,
[SuGrID] [smallint] NULL,
[SuppID] [bigint] NULL,
[SuPLProductCode] [varchar](25) NULL,
[SuPLEffectiveDate] [datetime] NULL,
[SuPLEndDate] [datetime] NULL,
[SuPLReason] [varchar](50) NULL,
[SuPLNSP] [decimal](20, 4) NULL,
[SuPLPromotionNSP] [decimal](20, 4) NULL,
[SuPLRSP] [decimal](20, 4) NULL,
[SuPLPromotionRSP] [decimal](20, 4) NULL,
[SuPLPalletNSP] [decimal](20, 4) NULL,
[SuPLPalletPromotionNSP] [decimal](20, 4) NULL,
[SuPLPromotionID] [int] NULL,
[SuPLPromotionRetail] [decimal](20, 4) NULL,
[SuPLPromotionSellStartDate_Original] [datetime] NULL,
[SuPLPromotionSellEndDate_Original] [datetime] NULL,
[SuPLPromotionSellStartDate] [datetime] NULL,
[SuPLPromotionSellEndDate] [datetime] NULL,
[SuPLPromotionOrderStartDate] [datetime] NULL,
[SuPLPromotionOrderEndDate] [datetime] NULL,
[SuPLVATCode] [varchar](12) NULL,
[SuPLVATRate] [decimal](20, 10) NULL,
[SuPLPOR] [decimal](20, 2) NULL,
[SuPLCurrency] [varchar](6) NULL,
[SuPLDivisionRMS] [int] NULL,
[SuPLCommodityGroup] [int] NULL,
[SuPLSubCommodityGroup] [int] NULL,
[SuPLsubSubCommodityGroup] [int] NULL,
[SuPLcatchWeight] [bit] NULL,
[SuPLPlof] [int] NULL,
[SuPLOwnLabel] [varchar](1) NULL,
[SuPLInnerEAN] [varchar](18) NULL,
[SuPLOuterEAN] [varchar](18) NULL,
[SuPLshortDescription] [varchar](50) NULL,
[SuPLlongDescription] [varchar](100) NULL,
[SuPLnumberUnitsPack] [decimal](20, 4) NULL,
[SuPLunitWeight] [decimal](20, 2) NULL,
[SuPLisPresell] [varchar](1) NULL,
[SuPLPalletSize] [decimal](12, 4) NULL,
[SuPLpackageUOM] [varchar](4) NULL,
[SuPLWeight] [decimal](12, 4) NULL,
[SuPLunitPriceMeasure] [varchar](20) NULL,
[SuPLWidth] [decimal](20, 4) NULL,
[SuPLdepth] [decimal](20, 4) NULL,
[SuPLheight] [decimal](20, 4) NULL,
[SuPLlwhUnitOfMeasure] [varchar](20) NULL,
[SuPLnetWeight] [decimal](20, 4) NULL,
[SuPLmaxOrderQuantity] [decimal](20, 4) NULL,
[SuPLblackListed] [varchar](1) NULL,
[SuPLprePricedFlag] [varchar](1) NULL,
[SuPLlastSubstitute] [varchar](25) NULL,
[SuPLlastRatio] [decimal](20, 2) NULL,
[SuPLrangeStatus] [varchar](1) NULL,
[SuPLpackDesc] [varchar](20) NULL,
[SuPLforecast] [varchar](1) NULL,
[SuPLlabel] [varchar](8) NULL,
[SuPLpricingUnitOfMeasure] [varchar](40) NULL,
[SuPLplofShelfLife] [smallint] NULL,
[SuPLPriceCheckIndPlus] [varchar](1) NULL,
[SuPLPriceCheckInd] [varchar](1) NULL,
[SuPLcoreRange] [varchar](1) NULL,
[SuPLcoreRangePlus] [bit] NULL,
[SuPLsurchargeIncludeFlag] [bit] NULL,
[SuPLsurchargeApplicableFlag] [bit] NULL,
[SuPLrspFactor] [int] NULL,
[SuPLcountryOfOriginID] [varchar](10) NULL,
[SuPLcountryOfOriginName] [varchar](100) NULL,
[SuPLrspOther] [varchar](1000) NULL,
[SuPLproductAttribute] [varchar](100) NULL,
[SuGDID] [smallint] NULL,
[ProdID] [int] NULL,
[ItGrID] [int] NULL,
[SuPLPricingStrategyType] [char](1) NULL,
[SuPLPricingStrategyValue] [decimal](7, 2) NULL,
[SuPLRoundingType] [char](1) NULL,
[SuPLRoundingPoint1] [tinyint] NULL,
[SuPLRoundingPoint2] [tinyint] NULL,
[SuPLRRP] [decimal](20, 4) NULL,
[SyReMessage] [varchar](1000) NULL,
[MyColumn] Varchar (10) NULL
)'
###
*/
Create PROCEDURE [dbo].[SPA_SystemUDTT_Update]
@ObjectName Varchar(1000),
@TableTypeScript Varchar(max)
AS
SET NOCOUNT ON
SET XACT_ABORT ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
Declare @TransName Varchar(100) = NewId()

Begin Try
Begin Transaction @TransName;

Declare @DependantSource As Table (RowId Bigint,[Object_Name] Varchar(1000),[Script] Varchar(max),[Type] Varchar(50))

If Object_Id('dbo.[SequenceSystemDependancy]') Is Not NUll
Begin
Drop SEQUENCE [DBO].[SequenceSystemDependancy]
End
CREATE SEQUENCE [DBO].[SequenceSystemDependancy] AS INT START WITH 1 INCREMENT BY 1

Insert Into @DependantSource
    SELECT NEXT VALUE FOR DBO.SequenceSystemDependancy, OBJECT_NAME(m.object_id) [Object_Name],m.Definition As Script,o.Type
    FROM sys.sql_expression_dependencies d
    JOIN sys.sql_modules m ON m.object_id = d.referencing_id
    JOIN sys.objects o ON o.object_id = m.object_id
    WHERE referenced_id = TYPE_ID(@ObjectName)
 
----///----------Alter Sequence to decending order -----------
---- =====================================================================================================================================
Declare @sqlSequence As Varchar(max) = ''
Set @sqlSequence = 'Alter SEQUENCE [DBO].[SequenceSystemDependancy] RESTART WITH '
+ Cast(
(Select  Current_Value FROM SYS.Sequences WHERE name='SequenceSystemDependancy')
As Varchar(10))+ ' INCREMENT BY -1 MINVALUE 0 MAXVALUE '
+ Cast(
(Select  Current_Value FROM SYS.Sequences WHERE name='SequenceSystemDependancy')
As Varchar(10))
execute (@sqlSequence);
Declare @ObjectNameR Varchar(1000)= Null,@ObjectTypeR Varchar(50) = NULL
While (NEXT VALUE FOR DBO.SequenceSystemDependancy>0)
Begin
Select @ObjectNameR = NULL,
@ObjectTypeR = NULL

Select @ObjectTypeR = p.[Type],@ObjectNameR = p.[Object_Name]
From @DependantSource p
Where p.RowId = cast((SELECT Current_Value FROM SYS.Sequences With(NoLock) WHERE name='SequenceSystemDependancy') As BigInt)

If Object_Id(@ObjectNameR) Is Not Null And @ObjectTypeR = 'P'
Begin
Execute ('Drop Proc '+@ObjectNameR);
End
End


execute ('Drop Type '+@ObjectName);
execute(@TableTypeScript);


Set @sqlSequence = 'Alter SEQUENCE [DBO].[SequenceSystemDependancy] RESTART WITH '
+ Cast(
(Select Count(1) From @DependantSource)
As Varchar(10))+ ' INCREMENT BY -1 MINVALUE 0'

execute (@sqlSequence);

Declare @ObjectNameC As Varchar(1000) ,@ObjectTypeC Varchar(50) ,@ObjectScriptC Varchar(max)
While (NEXT VALUE FOR DBO.SequenceSystemDependancy>0)
Begin
Select @ObjectNameC = NULL,
@ObjectTypeC = NULL,
@ObjectScriptC = NULL
Select @ObjectTypeC = p.[Type],@ObjectNameC = p.[Object_Name],@ObjectScriptC = p.Script
From @DependantSource p
Where p.RowId = cast((SELECT Current_Value FROM SYS.Sequences With(NoLock) WHERE name='SequenceSystemDependancy') As BigInt)

execute(@ObjectScriptC);
End
-- =====================================================================================================================================
--///----------Remove Sequence If Any-----------
-- =====================================================================================================================================
If Object_Id('DBO.SequenceSystemDependancy') Is Not Null
Begin
Drop SEQUENCE [DBO].[SequenceSystemDependancy]
End

Commit Transaction @TransName
End Try
Begin Catch
IF (@@TRANCOUNT > 0 )
BEGIN
IF (XACT_STATE()) = -1
BEGIN
ROLLBACK TRANSACTION @TransName
END
END
EXEC [SPBE_LogAndThrowException] 'SPA_System_UpdateUDTT';
End Catch