/****** Object: StoredProcedure [dbo].[vesp_RofstHeaderInsert] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstHeaderInsert] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataSearch] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDataSearch] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetValueDifferences] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDataGetValueDifferences] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetRofstByID] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDataGetRofstByID] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetDatabases] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDataGetDatabases] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetDatabaseByID] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDataGetDatabaseByID] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildrenByRoid] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDataGetChildrenByRoid] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildrenByRofstID] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDataGetChildrenByRofstID] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildrenByID] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDataGetChildrenByID] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildByRoid] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDataGetChildByRoid] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildByID] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDataGetChildByID] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildByAccPageID] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDataGetChildByAccPageID] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataExists] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDataExists] GO /****** Object: StoredProcedure [dbo].[vesp_RofstDatabaseInsert] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstDatabaseInsert] GO /****** Object: StoredProcedure [dbo].[vesp_RofstChildInsert] Script Date: 4/26/2022 6:59:49 AM ******/ DROP PROCEDURE IF EXISTS [dbo].[vesp_RofstChildInsert] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstHeader]') AND type in (N'U')) ALTER TABLE [dbo].[RofstHeader] DROP CONSTRAINT IF EXISTS [FK_RofstHeader_ROFsts] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstDefaultValue]') AND type in (N'U')) ALTER TABLE [dbo].[RofstDefaultValue] DROP CONSTRAINT IF EXISTS [FK_RofstDefaultValue_RofstHeader] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstDatabase]') AND type in (N'U')) ALTER TABLE [dbo].[RofstDatabase] DROP CONSTRAINT IF EXISTS [FK_RofstDatabase_RofstHeader] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstChild]') AND type in (N'U')) ALTER TABLE [dbo].[RofstChild] DROP CONSTRAINT IF EXISTS [FK_RofstChild_RofstDatabase] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstHeader]') AND type in (N'U')) ALTER TABLE [dbo].[RofstHeader] DROP CONSTRAINT IF EXISTS [DF_RofstHeader_CreateDate] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstHeader]') AND type in (N'U')) ALTER TABLE [dbo].[RofstHeader] DROP CONSTRAINT IF EXISTS [DF_RofstHeader_CreateUserID] GO IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[RofstDatabase]') AND type in (N'U')) ALTER TABLE [dbo].[RofstDatabase] DROP CONSTRAINT IF EXISTS [DF_RofstDatabase_ParentID] GO /****** Object: Index [IX_RofstID_Roid] Script Date: 4/26/2022 6:59:49 AM ******/ DROP INDEX IF EXISTS [IX_RofstID_Roid] ON [dbo].[RofstChild] GO /****** Object: Index [IX_RofstID_DbiID_ParentID] Script Date: 4/26/2022 6:59:49 AM ******/ DROP INDEX IF EXISTS [IX_RofstID_DbiID_ParentID] ON [dbo].[RofstChild] GO /****** Object: View [dbo].[vwRofstData_RofstDatabases] Script Date: 4/26/2022 6:59:49 AM ******/ DROP VIEW IF EXISTS [dbo].[vwRofstData_RofstDatabases] GO /****** Object: View [dbo].[vwRofstData_RofstHeaders] Script Date: 4/26/2022 6:59:49 AM ******/ DROP VIEW IF EXISTS [dbo].[vwRofstData_RofstHeaders] GO /****** Object: Table [dbo].[RofstHeader] Script Date: 4/26/2022 6:59:49 AM ******/ DROP TABLE IF EXISTS [dbo].[RofstHeader] GO /****** Object: View [dbo].[vwRofstData_RofstChildren] Script Date: 4/26/2022 6:59:49 AM ******/ DROP VIEW IF EXISTS [dbo].[vwRofstData_RofstChildren] GO /****** Object: View [dbo].[vwRofstData_RofstGroups] Script Date: 4/26/2022 6:59:49 AM ******/ DROP VIEW IF EXISTS [dbo].[vwRofstData_RofstGroups] GO /****** Object: Table [dbo].[RofstDatabase] Script Date: 4/26/2022 6:59:49 AM ******/ DROP TABLE IF EXISTS [dbo].[RofstDatabase] GO /****** Object: Table [dbo].[RofstChild] Script Date: 4/26/2022 6:59:49 AM ******/ DROP TABLE IF EXISTS [dbo].[RofstChild] GO /****** Object: View [dbo].[vwRofstData_RofstDefaultValues] Script Date: 4/26/2022 6:59:49 AM ******/ DROP VIEW IF EXISTS [dbo].[vwRofstData_RofstDefaultValues] GO /****** Object: Table [dbo].[RofstDefaultValue] Script Date: 4/26/2022 6:59:49 AM ******/ DROP TABLE IF EXISTS [dbo].[RofstDefaultValue] GO /****** Object: UserDefinedFunction [dbo].[vefn_RofstDataReplaceVars] Script Date: 4/26/2022 6:59:49 AM ******/ DROP FUNCTION IF EXISTS [dbo].[vefn_RofstDataReplaceVars] GO /****** Object: UserDefinedFunction [dbo].[vefn_RofstDataReplaceApplTagsWithDefaults] Script Date: 4/26/2022 6:59:49 AM ******/ DROP FUNCTION IF EXISTS [dbo].[vefn_RofstDataReplaceApplTagsWithDefaults] GO /****** Object: UserDefinedFunction [dbo].[vefn_NumOfOccurrences] Script Date: 4/26/2022 6:59:49 AM ******/ DROP FUNCTION IF EXISTS [dbo].[vefn_NumOfOccurrences] GO /****** Object: UserDefinedFunction [dbo].[vefn_Clean] Script Date: 4/26/2022 6:59:49 AM ******/ DROP FUNCTION IF EXISTS [dbo].[vefn_Clean] GO /****** Object: UserDefinedFunction [dbo].[vefn_Clean] Script Date: 4/26/2022 6:59:49 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO If Exists(Select * From sys.objects Where type = 'TF' And object_id = OBJECT_ID('[dbo].[vefn_ParseStringListToTable]')) Drop Function [dbo].[vefn_ParseStringListToTable]; SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ========================================================================================== -- Author: Jake Ropar -- Create Date: 3/26/2018 -- Description: Splits a delimited list of strings into a table of string values -- ========================================================================================== Create Function [dbo].[vefn_ParseStringListToTable] ( @List VarChar(Max), @Delimeter VarChar(1) = null ) Returns @RetVal Table (ListPosition Int Identity(1,1) Not Null, ListValue VarChar(Max) Not Null) With Execute As Owner As Begin Declare @Position Int; Declare @TextPosition Int; Declare @Length smallint; Declare @String VarChar(Max); Declare @TempString VarChar(Max); Declare @LeftOver VarChar(Max); Set @TextPosition = 1; Set @LeftOver = ''; Set @Delimeter = IsNull(@Delimeter, ','); If (DataLength(@List) = 1) Insert @RetVal (ListValue) Values (Cast(@List as VarChar(Max))); Else Begin While (@TextPosition <= DataLength(@List) / 2) Begin Set @Length = (4000 - DataLength(@LeftOver) / 2); Set @TempString = LTrim(@LeftOver + Substring(@List, @TextPosition, @Length)); Set @TextPosition = @TextPosition + @Length; Set @Position = CharIndex(@Delimeter, @TempString); While (@Position > 0) Begin Set @String = Substring(@TempString, 1, @Position - 1); Insert @RetVal (ListValue) Values (Cast(@String as VarChar(Max))); Set @TempString = LTrim(Substring(@TempString, @Position + 1, Len(@TempString))); Set @Position = CharIndex(@Delimeter, @TempString); End Set @LeftOver = @TempString; End End If (LTrim(RTrim(@LeftOver)) <> '') Insert @RetVal (ListValue) Values (Cast(@LeftOver as VarChar(Max))); Return; End Go -- ========================================================================================== -- Author: Jake Ropar -- Create Date: 4/21/2022 -- Description: Cleans (Trim) the string value passed -- Optional: Truncate is string is passed a specified max length -- Optional: Convert any null values to an empty string or leave as null -- ========================================================================================== Create Function [dbo].[vefn_Clean] ( @Value VarChar(Max), @ConvertNulls bit = 0, @MaxLength Int = null ) Returns VarChar(Max) As Begin Declare @RetVal VarChar(Max); If (Len(IsNull(@Value,'')) > 0) Begin Set @RetVal = LTrim(RTrim(@Value)); If (Not @MaxLength is null And Len(@RetVal) > @MaxLength) Set @RetVal = Left(@RetVal, @MaxLength); End If (@ConvertNulls = 1) Set @RetVal = IsNull(@RetVal, ''); Return @RetVal; End GO /****** Object: UserDefinedFunction [dbo].[vefn_NumOfOccurrences] Script Date: 4/26/2022 6:59:49 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ========================================================================================== -- Author: Jake Ropar -- Create Date: 3/1/2022 -- Description: Returns the number of occurences of a specific value in a specified string -- ========================================================================================== CREATE FUNCTION [dbo].[vefn_NumOfOccurrences](@Text VarChar(Max), @Value VarChar(Max)) Returns Int WITH EXECUTE AS OWNER AS BEGIN Declare @ValueCount Int; If (Len(@Value) = 0) Set @ValueCount = 0; Else Set @ValueCount = (Len(@Text) - Len(Replace(@Text, @Value, ''))) / Len(@Value); Return @ValueCount; END GO /****** Object: UserDefinedFunction [dbo].[vefn_RofstDataReplaceApplTagsWithDefaults] Script Date: 4/26/2022 6:59:49 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ========================================================================================== -- Author: Jake Ropar -- Create Date: 3/25/2022 -- Description: Replaces any "" tags with the default value -- ========================================================================================== CREATE FUNCTION [dbo].[vefn_RofstDataReplaceApplTagsWithDefaults](@Values VarChar(Max)) Returns VarChar(Max) WITH EXECUTE AS OWNER AS BEGIN Declare @RetVal VarChar(Max) = ''; Declare @DefaultValue VarChar(Max) = ''; Declare @StartTagIndex Int; Declare @EndTagIndex Int; Declare @StartTagValue VarChar(Max); Set @RetVal = @Values; Set @StartTagIndex = PatIndex('%%', @RetVal); While (@StartTagIndex > 0) Begin Set @StartTagValue = SubString(@RetVal, @StartTagIndex, (@EndTagIndex - @StartTagIndex) + 5); Set @DefaultValue = Replace(@StartTagValue, '%', @DefaultValue); Set @DefaultValue = RTrim(SubString(@DefaultValue, 0, @EndTagIndex)); Set @RetVal = Replace(@RetVal, @StartTagValue, @DefaultValue); Set @StartTagIndex = PatIndex('%%', @RetVal); End -- While (@StartTagIndex > 0) Return @RetVal; END GO /****** Object: UserDefinedFunction [dbo].[vefn_RofstDataReplaceVars] Script Date: 4/26/2022 6:59:49 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ========================================================================================== -- Author: Jake Ropar -- Create Date: 3/25/2022 -- Description: Replaces Any Variables and returns the rest of the value string -- ========================================================================================== CREATE FUNCTION [dbo].[vefn_RofstDataReplaceVars](@Values VarChar(Max)) Returns VarChar(Max) WITH EXECUTE AS OWNER AS BEGIN Declare @RetVal VarChar(Max) = ''; Declare @EqualsIndex Int; Declare @EndIndex Int; Declare @NameValPairStartIndex Int; Declare @NameValPairEndIndex Int; Declare @VarPair VarChar(Max); Declare @VarName VarChar(Max); Declare @VarValue VarChar(Max); -- Replace Any "" Tags with the Default Value first Select @RetVal = dbo.vefn_RofstDataReplaceApplTagsWithDefaults(@Values); If (PatIndex('%{{A%', @RetVal) > 0) Set @EndIndex = PatIndex('%{{A%', @RetVal); Else Set @EndIndex = Len(@RetVal) -1; Set @NameValPairStartIndex = PatIndex('%{%', @RetVal); Set @NameValPairEndIndex = PatIndex('%}%', @RetVal); While(@NameValPairStartIndex > 0 And @NameValPairStartIndex < @EndIndex) Begin -- Get Name Value Pair [ex. {EGS=1214}] Set @VarPair = SubString(@RetVal, @NameValPairStartIndex, (@NameValPairEndIndex - @NameValPairStartIndex) + 1); -- Remove Name Value Pair From Return Val Set @RetVal = Replace(@RetVal, @VarPair, ''); -- Get Variable Name and Value Set @EqualsIndex = PatIndex('%=%', @VarPair); If (@EqualsIndex > 0) Begin Set @VarName = SubString(@VarPair, 2, @EqualsIndex - 2); Set @VarValue = SubString(@VarPair, @EqualsIndex + 1, Len(@VarPair) - @EqualsIndex - 1); Set @VarName = Concat('{', @VarName, '}'); -- Replace All Occurences Set @RetVal = Replace(@RetVal, @VarName, @VarValue); End -- Get Updated Index Values If (PatIndex('%{{A%', @RetVal) > 0) Set @EndIndex = PatIndex('%{{A%', @RetVal); Else Set @EndIndex = Len(@RetVal) -1; Set @NameValPairStartIndex = PatIndex('%{%', @RetVal); Set @NameValPairEndIndex = PatIndex('%}%', @RetVal); End -- End While(@ReplaceVarEndTagIndex > 0) Return @RetVal; END GO /****** Object: Table [dbo].[RofstDefaultValue] Script Date: 4/26/2022 6:59:49 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[RofstDefaultValue]( [RofstID] [int] NOT NULL, [roid] [varchar](50) NOT NULL, [value] [varchar](max) NOT NULL, [AccPageID] [varchar](max) NULL, CONSTRAINT [PK_RofstDefaultValue] PRIMARY KEY CLUSTERED ( [RofstID] ASC, [roid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: View [dbo].[vwRofstData_RofstDefaultValues] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2012 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2024 Description: (RofstData) Master View for Rofst Roid Default Values ========================================================================================================== */ Create View [dbo].[vwRofstData_RofstDefaultValues] As Select rdv.RofstID as 'RofstID', rdv.roid as 'roid', rdv.[value] as 'value', rdv.AccPageID as 'AccPageID' From RofstDefaultValue rdv With (NoLock) GO /****** Object: Table [dbo].[RofstChild] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[RofstChild]( [RofstChildID] [int] IDENTITY(1,1) NOT NULL, [RofstID] [int] NOT NULL, [dbiID] [int] NOT NULL, [ID] [int] NOT NULL, [ParentID] [int] NOT NULL, [type] [int] NOT NULL, [title] [varchar](max) NOT NULL, [roid] [varchar](50) NOT NULL, [appid] [varchar](max) NULL, [value] [varchar](max) NULL, CONSTRAINT [PK_RofstChild] PRIMARY KEY CLUSTERED ( [RofstChildID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: Table [dbo].[RofstDatabase] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[RofstDatabase]( [RofstID] [int] NOT NULL, [dbiID] [int] NOT NULL, [dbiType] [int] NOT NULL, [dbiAW] [int] NOT NULL, [dbiTitle] [varchar](max) NOT NULL, [dbiAP] [varchar](max) NOT NULL, [ID] [int] NOT NULL, [ParentID] [int] NOT NULL, CONSTRAINT [PK_RofstDatabase_1] PRIMARY KEY CLUSTERED ( [RofstID] ASC, [dbiID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: View [dbo].[vwRofstData_RofstGroups] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2012 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2024 Description: (RofstData) Master View for RofstGroups ========================================================================================================== */ Create View [dbo].[vwRofstData_RofstGroups] As Select rd.RofstID as 'RofstID', -- RoDatabase Info rd.dbiID as 'dbiID', rd.dbiType as 'dbiType', rd.dbiAW as 'dbiAW', rd.dbiTitle as 'dbiTitle', rd.dbiAP as 'dbiAP', -- RoChild Info rc.ID as 'ID', rc.ParentID as 'ParentID', rc.[type] as 'type', rc.title as 'title', rc.roid as 'riod' From RofstChild rc With (NoLock) inner join RofstDatabase rd With (NoLock) on rc.RofstID = rd.RofstID and rc.dbiID = rd.dbiID Where rc.[value] is null; GO /****** Object: View [dbo].[vwRofstData_RofstChildren] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2012 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2024 Description: (RofstData) Master View for RofstChildren (RoChild & Groups) ========================================================================================================== */ Create View [dbo].[vwRofstData_RofstChildren] As Select rc.RofstChildID as 'RofstChildID', rd.RofstID as 'RofstID', -- RoDatabase Info rd.dbiID as 'dbiID', rd.dbiType as 'dbiType', rd.dbiAW as 'dbiAW', rd.dbiTitle as 'dbiTitle', rd.dbiAP as 'dbiAP', -- RoChild Info rc.ID as 'ID', rc.ParentID as 'ParentID', rc.[type] as 'type', rc.title as 'title', rc.roid as 'roid', rc.appid as 'appid', rc.[value] as 'value', Case When (Not rc.[value] is null) Then rd.dbiAP + '-' + rc.appid Else null End as 'AccPageID' From RofstChild rc With (NoLock) inner join RofstDatabase rd With (NoLock) on rc.RofstID = rd.RofstID and rc.dbiID = rd.dbiID; GO /****** Object: Table [dbo].[RofstHeader] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[RofstHeader]( [RofstID] [int] NOT NULL, [hSize] [int] NOT NULL, [hMonth] [int] NOT NULL, [hDay] [int] NOT NULL, [hcYear] [int] NOT NULL, [hcMonth] [int] NOT NULL, [hcDay] [int] NOT NULL, [hcHour] [int] NOT NULL, [hcMin] [int] NOT NULL, [hcSec] [int] NOT NULL, [hcHund] [int] NOT NULL, [ROLookup] [varbinary](max) NULL, [CreateUserID] [varchar](50) NOT NULL, [CreateDate] [datetime] NOT NULL, CONSTRAINT [PK_RofstHeader] PRIMARY KEY CLUSTERED ( [RofstID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO /****** Object: View [dbo].[vwRofstData_RofstHeaders] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2012 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2024 Description: (RofstData) Master View for RofstHeaders ========================================================================================================== */ Create View [dbo].[vwRofstData_RofstHeaders] As Select rh.RofstID as 'RofstID', rh.hSize as 'hSize', rh.hMonth as 'hMonth', rh.hDay as 'hDay', rh.hcYear as 'hcYear', rh.hcMonth as 'hcMonth', rh.hcDay as 'hcDay', rh.hcHour as 'hcHour', rh.hcMin as 'hcMin', rh.hcSec as 'hcSec', rh.hcHund as 'hcHund', rh.CreateUserID as 'CreateUserID', rh.CreateDate as 'CreateDate' From RofstHeader rh With (NoLock) GO /****** Object: View [dbo].[vwRofstData_RofstDatabases] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2012 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2024 Description: (RofstData) Master View for RofstDatabases ========================================================================================================== */ Create View [dbo].[vwRofstData_RofstDatabases] As Select rd.RofstID as 'RofstID', rd.dbiID as 'dbiID', rd.dbiType as 'dbiType', rd.dbiAW as 'dbiAW', rd.dbiTitle as 'dbiTitle', rd.dbiAP as 'dbiAP', rd.ID as 'ID', rd.ParentID as 'ParentID' From RofstDatabase rd With (NoLock) GO /****** Object: Index [IX_RofstID_DbiID_ParentID] Script Date: 4/26/2022 6:59:50 AM ******/ CREATE NONCLUSTERED INDEX [IX_RofstID_DbiID_ParentID] ON [dbo].[RofstChild] ( [RofstID] ASC, [dbiID] ASC, [ParentID] ASC ) INCLUDE([ID],[type],[title],[roid],[appid],[value]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO SET ANSI_PADDING ON GO /****** Object: Index [IX_RofstID_Roid] Script Date: 4/26/2022 6:59:50 AM ******/ CREATE NONCLUSTERED INDEX [IX_RofstID_Roid] ON [dbo].[RofstChild] ( [RofstID] ASC, [roid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ALTER TABLE [dbo].[RofstDatabase] ADD CONSTRAINT [DF_RofstDatabase_ParentID] DEFAULT ((0)) FOR [ParentID] GO ALTER TABLE [dbo].[RofstHeader] ADD CONSTRAINT [DF_RofstHeader_CreateUserID] DEFAULT ('SYSTEM') FOR [CreateUserID] GO ALTER TABLE [dbo].[RofstHeader] ADD CONSTRAINT [DF_RofstHeader_CreateDate] DEFAULT (getdate()) FOR [CreateDate] GO ALTER TABLE [dbo].[RofstChild] WITH CHECK ADD CONSTRAINT [FK_RofstChild_RofstDatabase] FOREIGN KEY([RofstID], [dbiID]) REFERENCES [dbo].[RofstDatabase] ([RofstID], [dbiID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[RofstChild] CHECK CONSTRAINT [FK_RofstChild_RofstDatabase] GO ALTER TABLE [dbo].[RofstDatabase] WITH CHECK ADD CONSTRAINT [FK_RofstDatabase_RofstHeader] FOREIGN KEY([RofstID]) REFERENCES [dbo].[RofstHeader] ([RofstID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[RofstDatabase] CHECK CONSTRAINT [FK_RofstDatabase_RofstHeader] GO ALTER TABLE [dbo].[RofstDefaultValue] WITH CHECK ADD CONSTRAINT [FK_RofstDefaultValue_RofstHeader] FOREIGN KEY([RofstID]) REFERENCES [dbo].[RofstHeader] ([RofstID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[RofstDefaultValue] CHECK CONSTRAINT [FK_RofstDefaultValue_RofstHeader] GO ALTER TABLE [dbo].[RofstHeader] WITH CHECK ADD CONSTRAINT [FK_RofstHeader_ROFsts] FOREIGN KEY([RofstID]) REFERENCES [dbo].[ROFsts] ([ROFstID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[RofstHeader] CHECK CONSTRAINT [FK_RofstHeader_ROFsts] GO /****** Object: StoredProcedure [dbo].[vesp_RofstChildInsert] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstChildInsert] ( @RofstID Int, @ID Int, @ParentID Int, @dbiID Int, @type Int, @title VarChar(Max), @roid VarChar(50), @appid VarChar(Max) = null, @value VarChar(Max) = null ) With Execute as Owner As Begin Declare @AccPageID VarChar(Max) = null; Declare @DefaultValues VarChar(Max); -- Create Rofst Child/Group Record Insert Into RofstChild (RofstID, ID, ParentID, dbiID, [type], title, roid, appid, [value]) Values (@RofstID, @ID, @ParentID, @dbiID, @type, @title, @roid, @appid, @value); -- Check for appid, if exists, then insert the default value for each return type if multi-value If (Len(@appid) > 0) Begin -- Get Accessory Page ID Select @AccPageID = Concat(d.dbiAP, '-', @appid) From RofstDatabase d with (NoLock) Where d.RofstID = @RofstID And d.dbiID = @dbiID; -- Insert Rofst Default Value (Parent RoChild) [roid = 12] Insert Into RofstDefaultValue (RofstID, roid, [value], AccPageID) Values (@RofstID, @roid, Replace(@title, '\u160?', ' '), @AccPageID); -- Insert Rofst Default Value(s) (Children RoChild) [roid = 16] (Do Not Insert Duplicates) Select @DefaultValues = Replace(dbo.vefn_RofstDataReplaceVars(@value), '{', ''); With ChildrenValues as ( Select (x.ListPosition - 1) as 'OffsetIndex', Case When (PatIndex('%=%', x.ListValue) > 0) Then Right(x.ListValue, Len(x.ListValue)-PatIndex('%=%', x.ListValue)) Else x.ListValue End as 'DefaultValue' From [dbo].[vefn_ParseStringListToTable](@DefaultValues, '}') x Where Len(x.ListValue) > 0 ) Insert Into RofstDefaultValue (RofstID, roid, [value], AccPageID) Select @RofstID as 'RofstID', Concat(@roid, Cast(Format(Min(OffsetIndex), 'D4') as VarChar(4))) as 'roid', DefaultValue as 'value', null as 'AccPageID' From ChildrenValues Group By DefaultValue Order By Min(OffsetIndex) Asc End -- (Len(@appid) > 0) Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDatabaseInsert] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDatabaseInsert] ( @RofstID Int, @dbiID Int, @dbiType Int, @dbiAW Int, @dbiTitle VarChar(Max), @dbiAP VarChar(Max), @ID Int, @ParentID Int ) With Execute as Owner As Begin Insert Into RofstDatabase ( RofstID, dbiID, dbiType, dbiAW, dbiTitle, dbiAP, ID, ParentID) Values ( @RofstID, @dbiID, @dbiType, @dbiAW, @dbiTitle, @dbiAP, @ID, @ParentID); Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataExists] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDataExists] ( @RofstID int ) With Execute as Owner As Begin Select Count(1) From RofstHeader with (NoLock) Where RofstID = @RofstID; Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildByAccPageID] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDataGetChildByAccPageID] ( @RofstID int, @AccPageID VarChar(50) ) With Execute as Owner As Begin Select rc.ROFstID, rc.dbiID, rc.ID, rc.ParentID, rc.[type], rc.title, rc.roid, rc.appid, rc.[value], rc.AccPageID From vwRofstData_RofstChildren rc Where rc.RofstID = @RofstID And rc.AccPageID = @AccPageID; Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildByID] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDataGetChildByID] ( @RofstID int, @DbiID int, @ID Int ) With Execute as Owner As Begin Select rc.RofstID, rc.dbiID, rc.ID, rc.ParentID, rc.[type], rc.title, rc.roid, rc.appid, rc.[value], rc.AccPageID From vwRofstData_RofstChildren rc Where rc.RofstID = @RofstID And rc.dbiID = @DbiID And rc.ID = @ID; Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildByRoid] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDataGetChildByRoid] ( @RofstID int, @Roid VarChar(50) ) With Execute as Owner As Begin Select rc.ROFstID, rc.dbiID, rc.ID, rc.ParentID, rc.[type], rc.title, rc.roid, rc.appid, rc.[value], rc.AccPageID From vwRofstData_RofstChildren rc Where rc.RofstID = @RofstID And rc.roid = @Roid; Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildrenByID] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDataGetChildrenByID] ( @RofstID int, @DbiID int, @ID Int ) With Execute as Owner As Begin Select rc.RofstID, rc.dbiID, rc.ID, rc.ParentID, rc.[type], rc.title, rc.roid, rc.appid, rc.[value], rc.AccPageID From vwRofstData_RofstChildren rc Where rc.RofstID = @RofstID And rc.dbiID = @DbiID And rc.ParentID = @ID Order By rc.ID Asc; Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildrenByRofstID] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDataGetChildrenByRofstID] ( @RofstID int ) With Execute as Owner As Begin Select rc.RofstID, rc.dbiID, rc.ID, rc.ParentID, rc.[type], rc.title, rc.roid, rc.appid, rc.[value], rc.AccPageID From vwRofstData_RofstChildren rc Where rc.RofstID = @RofstID And Not rc.[value] is null Order By rc.dbiID Asc, rc.[type] Asc, rc.roid Asc; Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetChildrenByRoid] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDataGetChildrenByRoid] ( @RofstID int, @Roid VarChar(50) ) With Execute as Owner As Begin With RoParent as ( Select RofstID, dbiID, ID, [type], title, roid From vwRofstData_RofstChildren Where RofstID = @RofstID And roid = @Roid ) Select rc.RofstID, rc.dbiID, -- Ro Parent Info rp.[type] as 'ParentType', rp.title as 'ParentTitle', rp.roid as 'ParentRoid', -- Ro Child Info rc.ID, rc.ParentID, rc.[type], rc.title, rc.roid, rc.appid, rc.[value], rc.AccPageID From RoParent rp inner join vwRofstData_RofstChildren rc on rc.RofstID = rp.RofstID and rc.dbiID = rp.dbiID and rc.ParentID = rp.ID Order By rc.ID Asc; Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetDatabaseByID] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDataGetDatabaseByID] ( @RofstID int, @dbiID int ) With Execute as Owner As Begin Select rd.RofstID, rd.dbiID, rd.dbiType, rd.dbiAW, rd.dbiTitle, rd.dbiAP, rd.ID, rd.ParentID From vwRofstData_RofstDatabases rd Where rd.RofstID = @RofstID And rd.dbiID = @dbiID; Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetDatabases] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDataGetDatabases] ( @RofstID int ) With Execute as Owner As Begin Select rd.RofstID, rd.dbiID, rd.dbiType, rd.dbiAW, rd.dbiTitle, rd.dbiAP, rd.ID, rd.ParentID From vwRofstData_RofstDatabases rd Where rd.RofstID = @RofstID Order By rd.dbiID Asc; Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetRofstByID] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDataGetRofstByID] ( @RofstID int, @IncludeRoLookup Bit ) With Execute as Owner As Begin Select r.ROFstID, r.RODbID, Case (@IncludeRoLookup) When 1 Then r.ROLookup Else null End as 'ROLookup', r.DTS, r.UserID, r.LastChanged From ROFsts r with (NoLock) Where r.RofstID = @RofstID; Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataGetValueDifferences] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDataGetValueDifferences] ( @OriginalRofstID Int, @CurrentRofstID Int ) With Execute as Owner As Begin Declare @Differences Table (OriginalRoid VarChar(25), OriginalValue VarChar(Max), CurrentRoid VarChar(25), CurrentValue VarChar(Max)); With OriginalRofst as ( Select rc.roid, rc.[value] From vwRofstData_RofstChildren rc Where rc.RofstID = @OriginalRofstID And Not rc.[value] is null ), CurrentRofst as ( Select rc.roid, rc.[value] From vwRofstData_RofstChildren rc Where rc.RofstID = @CurrentRofstID And Not rc.[value] is null ) Insert Into @Differences (OriginalRoid, OriginalValue, CurrentRoid, CurrentValue) Select o.roid, o.[value], c.roid, c.[value] From OriginalRofst o left outer join CurrentRofst c on c.roid = o.roid Where o.[value] <> c.[value]; Select OriginalRoid as 'Roid', Case When (CurrentValue is null) Then 1 Else 0 End as 'IsDeleted', Case When (Not CurrentValue is null) Then 1 Else 0 End as 'IsModified' From @Differences; Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstDataSearch] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: RO Default Value Search @SearchTypeID => (1) Starts With (2) Ends With (3) Contains (4) Exact Match ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstDataSearch] ( @RofstID int, @Value VarChar(Max) = null, @SearchTypeID Int = 1 -- Starts With ) With Execute as Owner As Begin -- Remove any leading/trailing white spaces Set @Value = dbo.vefn_Clean( @Value, 0, null ); -- Escape any % signs in the search value Set @Value = Replace( @Value, '%', '[%]' ); -- Format search value If (Len(@Value) = 0) Set @Value = null; Select rdv.roid, rdv.[value] From vwRofstData_RofstDefaultValues rdv Where rdv.RofstID = @RofstID And ((@Value is null) Or (@SearchTypeID = 1 And rdv.[value] like @Value + '%') -- Starts With Or (@SearchTypeID = 2 And rdv.[value] like '%' + @Value) -- Ends With Or (@SearchTypeID = 3 And rdv.[value] like '%' + @Value + '%') -- Contains Or (@SearchTypeID = 4 And rdv.[value] = @Value)) -- Exact Match Option (Recompile); Return; End GO /****** Object: StoredProcedure [dbo].[vesp_RofstHeaderInsert] Script Date: 4/26/2022 6:59:50 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /***************************************************************************** Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE Copyright 2020 - Volian Enterprises, Inc. All rights reserved. *****************************************************************************/ /* ========================================================================================================== Author: Jake Ropar Create Date: 03/24/2022 Description: ========================================================================================================== */ Create Procedure [dbo].[vesp_RofstHeaderInsert] ( @RofstID Int, @hSize Int, @hMonth Int, @hDay Int, @hcYear Int, @hcMonth Int, @hcDay Int, @hcHour Int, @hcMin Int, @hcSec Int, @hcHund Int, @UserID VarChar(50) ) With Execute as Owner As Begin Insert Into RofstHeader (RofstID, hSize, hMonth, hDay, hcYear, hcMonth, hcDay, hcHour, hcMin, hcSec, hcHund, ROLookup, CreateUserID, CreateDate) Values (@RofstID, @hSize, @hMonth, @hDay, @hcYear, @hcMonth, @hcDay, @hcHour, @hcMin, @hcSec, @hcHund, null, @UserID, GetDate()); -- Copy over Binary Update RofstHeader Set ROLookup = r.ROLookup From ROFsts r with (NoLock) Where r.ROFstID = @RofstID; -- Delete from Main ROFsts for now Update ROFsts Set ROLookup = null Where ROFstID = @RofstID; Return; End GO /* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/ BEGIN TRANSACTION SET QUOTED_IDENTIFIER ON SET ARITHABORT ON SET NUMERIC_ROUNDABORT OFF SET CONCAT_NULL_YIELDS_NULL ON SET ANSI_NULLS ON SET ANSI_PADDING ON SET ANSI_WARNINGS ON COMMIT BEGIN TRANSACTION GO ALTER TABLE dbo.ROFsts DROP CONSTRAINT FK_ROFsts_RODbs GO ALTER TABLE dbo.RODbs SET (LOCK_ESCALATION = TABLE) GO COMMIT select Has_Perms_By_Name(N'dbo.RODbs', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.RODbs', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.RODbs', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION GO ALTER TABLE dbo.ROFsts DROP CONSTRAINT DF_Table_1_DTS1 GO ALTER TABLE dbo.ROFsts DROP CONSTRAINT DF_ROFsts_UserID GO CREATE TABLE dbo.Tmp_ROFsts ( ROFstID int NOT NULL IDENTITY (1, 1), RODbID int NOT NULL, ROLookup varbinary(MAX) NULL, Config nvarchar(MAX) NULL, DTS datetime NOT NULL, UserID nvarchar(100) NOT NULL, LastChanged timestamp NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE dbo.Tmp_ROFsts SET (LOCK_ESCALATION = TABLE) GO DECLARE @v sql_variant SET @v = N'{datetime}' EXECUTE sp_addextendedproperty N'MS_Description', @v, N'SCHEMA', N'dbo', N'TABLE', N'Tmp_ROFsts', N'COLUMN', N'DTS' GO ALTER TABLE dbo.Tmp_ROFsts ADD CONSTRAINT DF_Table_1_DTS1 DEFAULT (getdate()) FOR DTS GO ALTER TABLE dbo.Tmp_ROFsts ADD CONSTRAINT DF_ROFsts_UserID DEFAULT (upper(suser_sname())) FOR UserID GO SET IDENTITY_INSERT dbo.Tmp_ROFsts ON GO IF EXISTS(SELECT * FROM dbo.ROFsts) EXEC('INSERT INTO dbo.Tmp_ROFsts (ROFstID, RODbID, ROLookup, Config, DTS, UserID) SELECT ROFstID, RODbID, ROLookup, Config, DTS, UserID FROM dbo.ROFsts WITH (HOLDLOCK TABLOCKX)') GO SET IDENTITY_INSERT dbo.Tmp_ROFsts OFF GO ALTER TABLE dbo.Figures DROP CONSTRAINT FK_Figures_ROFsts GO ALTER TABLE dbo.Associations DROP CONSTRAINT FK_Associations_ROFsts GO ALTER TABLE dbo.RofstHeader DROP CONSTRAINT FK_RofstHeader_ROFsts GO DROP TABLE dbo.ROFsts GO EXECUTE sp_rename N'dbo.Tmp_ROFsts', N'ROFsts', 'OBJECT' GO ALTER TABLE dbo.ROFsts ADD CONSTRAINT PK_ROFsts PRIMARY KEY CLUSTERED ( ROFstID ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE UNIQUE NONCLUSTERED INDEX IX_ROFsts ON dbo.ROFsts ( RODbID, DTS ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO ALTER TABLE dbo.ROFsts ADD CONSTRAINT FK_ROFsts_RODbs FOREIGN KEY ( RODbID ) REFERENCES dbo.RODbs ( RODbID ) ON UPDATE NO ACTION ON DELETE NO ACTION GO COMMIT select Has_Perms_By_Name(N'dbo.ROFsts', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.ROFsts', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.ROFsts', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION GO ALTER TABLE dbo.RofstHeader ADD CONSTRAINT FK_RofstHeader_ROFsts FOREIGN KEY ( RofstID ) REFERENCES dbo.ROFsts ( ROFstID ) ON UPDATE NO ACTION ON DELETE CASCADE GO ALTER TABLE dbo.RofstHeader SET (LOCK_ESCALATION = TABLE) GO COMMIT select Has_Perms_By_Name(N'dbo.RofstHeader', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.RofstHeader', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.RofstHeader', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION GO ALTER TABLE dbo.Associations ADD CONSTRAINT FK_Associations_ROFsts FOREIGN KEY ( ROFstID ) REFERENCES dbo.ROFsts ( ROFstID ) ON UPDATE NO ACTION ON DELETE NO ACTION GO ALTER TABLE dbo.Associations SET (LOCK_ESCALATION = TABLE) GO COMMIT select Has_Perms_By_Name(N'dbo.Associations', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.Associations', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.Associations', 'Object', 'CONTROL') as Contr_Per BEGIN TRANSACTION GO ALTER TABLE dbo.Figures ADD CONSTRAINT FK_Figures_ROFsts FOREIGN KEY ( ROFstID ) REFERENCES dbo.ROFsts ( ROFstID ) ON UPDATE NO ACTION ON DELETE NO ACTION GO ALTER TABLE dbo.Figures SET (LOCK_ESCALATION = TABLE) GO COMMIT select Has_Perms_By_Name(N'dbo.Figures', 'Object', 'ALTER') as ALT_Per, Has_Perms_By_Name(N'dbo.Figures', 'Object', 'VIEW DEFINITION') as View_def_Per, Has_Perms_By_Name(N'dbo.Figures', 'Object', 'CONTROL') as Contr_Per