if not exists (select * from sysobjects where type = 'P' AND name = N'TransformManagedTypeDimProc')
begin
execute ('create procedure dbo.[TransformManagedTypeDimProc] @WaterMark xml as return 1')
end
go
DECLARE @Statement NVARCHAR(MAX)
SET @Statement=
'alter procedure dbo.TransformManagedTypeDimProc (@WaterMark xml)
as
begin
set nocount on
DECLARE @utc DATETIME = GETUTCDATE()
-- Get Metadata
declare @BatchId int = 0, @Updated int = 0, @Inserted int, @ModuleName nvarchar(128),
@source1WM datetime, @source1MaxWM datetime, @retval int, @err int, @nullguid uniqueidentifier
set @nullguid = ''00000000-0000-0000-0000-000000000000''
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'U' AND name = 'TablePartition' )
BEGIN
EXEC sp_executesql @Statement
END
GO
IF object_id (N'dbo.GetFirstLevelConcreteDerivedManagedTypes') IS NOT NULL
DROP function dbo.GetFirstLevelConcreteDerivedManagedTypes
GO
DECLARE @Statement1 NVARCHAR(MAX)
SET @Statement1 =
N'
CREATE FUNCTION dbo.GetFirstLevelConcreteDerivedManagedTypes
(
@ManagedTypeId uniqueidentifier
)
returns table
as
return (
with DerivedTypes(ManagedTypeId, DerivedManagedTypeId, Depth, IsAbstract) as
(
select ManagedTypeId as ManagedTypeId
,ManagedTypeId as DerivedManagedTypeId
,0 as Depth
,IsAbstract
from dbo.ManagedTypeDim
where ManagedTypeId = @ManagedTypeId
UNION ALL
select a.BaseManagedTypeId as ManagedTypeId
,a.ManagedTypeId as DerivedManagedTypeId
,b.Depth + 1 as Depth
,a.IsAbstract
from dbo.ManagedTypeDim a
join DerivedTypes b on a.BaseManagedTypeId=b.DerivedManagedTypeId
where b.IsAbstract = 1
)
select DerivedManagedTypeId from DerivedTypes a where a.IsAbstract = 0)'
IF EXISTS(SELECT * FROM sys.objects WHERE type = 'U' AND name = 'TablePartition' )
BEGIN
EXEC sp_executesql @Statement1
END
GO