Hi Community,
I've recently been working with MS support to get cleanup scripts for newer versions of Management Reporter.
Remember to test in non-prod before running in production, and take backups! FYI - This is not for the Data Mart.
Enjoy!
SET QUOTED_IDENTIFIER ON;
IF OBJECT_ID('tempdb..#MRScriptVariables') IS NOT NULL
DROP TABLE #MRScriptVariables
Create table #MRScriptVariables (VID varchar(255), PRID varchar(255), ID varchar(255), VKeep int, [Delete] bit, CDate datetime)
declare @VID varchar(255),@PRID varchar(255),@ID varchar(255),@VKeep int,@Delete bit,@CDate datetime
select @VID = '',@PRID = '',@ID = '',
--########################## ENTER VARIABLES ###############################################################
@VKeep = 1, -- Number of report versions to keep
@CDate = '2017-06-01', -- Only delete reports generated prior to this date
@Delete = 1 -- Set to 1 to delete or 0 to just print versions that will delete
--##########################################################################################################
insert into #MRScriptVariables values (@VID,@PRID,@ID,@VKeep,@Delete,@CDate)
ALTER DATABASE ManagementReporter SET RECOVERY SIMPLE
GO
DBCC TRACEON(610)
GO
IF OBJECT_ID('tempdb..##MRReportsToDelete') IS NOT NULL
DROP TABLE ##MRReportsToDelete
GO
---------------Create stored procedure
IF OBJECT_ID('tempdb..#pRepositoryDelete') IS NOT NULL
DROP PROCEDURE #pRepositoryDelete
GO
CREATE PROCEDURE [dbo].[#pRepositoryDelete] (@RepositoryID uniqueidentifier)
AS
BEGIN TRANSACTION;
IF NOT EXISTS (SELECT * FROM tempdb..sysobjects WHERE Name = N'##MRReportsToDelete')
CREATE TABLE ##MRReportsToDelete ( ReportId INT)
DECLARE @ID UNIQUEIDENTIFIER
DECLARE @ReportID INT
DECLARE @GroupId INT
SELECT ra.[RepositoryID], r.[ReportID] AS [ReportID], rgrl.[GroupId] as [GroupId], ra.[Distance] AS [Distance] INTO #temp FROM [Reporting].[RepositoryAncestor] ra
INNER JOIN [Reporting].[Repository] rep ON ra.[RepositoryID] = rep.[ID]
LEFT JOIN [Reporting].[ReportRepositoryLink] r ON r.[RepositoryID] = ra.[RepositoryID]
LEFT JOIN [Reporting].[ReportGroupRepositoryLink] rgrl ON rgrl.[RepositoryId] = ra.[RepositoryID]
WHERE ra.[AncestorRepositoryID] = @RepositoryID AND rep.[Type] <> 50
DECLARE items CURSOR LOCAL STATIC READ_ONLY FOR SELECT [RepositoryID], [ReportID], [GroupId] FROM #temp ORDER BY [Distance] DESC
OPEN items
FETCH NEXT FROM items INTO @ID, @ReportID, @GroupId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF @ReportID IS NOT NULL
BEGIN
DECLARE @CountLink INT
SELECT @CountLink = COUNT([RepositoryID]) FROM [Reporting].[ReportRepositoryLink] WHERE [ReportID] = @ReportID
IF @CountLink = 1
BEGIN
INSERT INTO ##MRReportsToDelete VALUES (@ReportID)
-- Delete related Flavor
INSERT INTO ##MRReportsToDelete SELECT l.[ReportID] FROM [Reporting].[ReportRepositoryLink] l INNER JOIN [Reporting].[Repository] r ON r.[ID] = l.RepositoryID WHERE r.[ParentRepositoryID] = @ID
DELETE FROM [Reporting].[Repository] WHERE [ParentRepositoryID] = @ID
END
ELSE
BEGIN
DECLARE @BackupRepositoryID uniqueidentifier
SELECT TOP (1) @BackupRepositoryID = [RepositoryID] FROM [Reporting].[ReportRepositoryLink] WHERE [ReportID] = @ReportID AND [RepositoryID] <> @ID
UPDATE [Reporting].[Repository] SET [ParentRepositoryID] = @BackupRepositoryID WHERE [ParentRepositoryID] = @ID
UPDATE [Reporting].[RepositoryReportFlavor] SET [ParentRepositoryID] = @BackupRepositoryID WHERE [ParentRepositoryID] = @ID
UPDATE [Reporting].[RepositoryReportFlavor] SET [RepositoryId] = @BackupRepositoryID WHERE [RepositoryId] = @ID
UPDATE [Reporting].[RepositoryLink] SET [ParentRepositoryID] = @BackupRepositoryID WHERE [ParentRepositoryID] = @ID
END
END
IF @GroupId IS NOT NULL
BEGIN
DECLARE @CountGroupLink INT
SELECT @CountGroupLink = COUNT([RepositoryId]) FROM [Reporting].[ReportGroupRepositoryLink] WHERE [GroupId] = @GroupId
IF @CountGroupLink = 1
BEGIN
EXEC [Reporting].[ReportGroupDelete] @GroupId
END
END
DELETE FROM [Reporting].[Repository] WHERE (ID = @ID)
FETCH NEXT FROM items INTO @ID, @ReportID, @GroupId
END DEALLOCATE items
COMMIT TRANSACTION;
GO
------Delete Repository records; store ReportId's to be deleted later
declare @VID varchar(255),
@PRID varchar(255),
@ID varchar(255),
@VKeep int,
@Delete bit,
@CDate datetime
declare @VPID as table (ID varchar(255))
----DO NOT EDIT-------------------
select top 1 @VID = v.VID,
@PRID = v.PRID,
@ID = v.ID,
@VKeep = v.VKeep,
@CDate = v.CDate,
@Delete = v.[Delete]
from #MRScriptVariables v
----DO NOT EDIT-------------------
declare version_id cursor for
select distinct VersionCorrelationId, ParentRepositoryID
from [Reporting].[Repository] (nolock)
where type in (10,12)
open version_id
fetch next from version_id into @VID, @PRID
while (@@FETCH_STATUS = 0)
begin
Insert into @VPID Select ID from [Reporting].[Repository] nolock where VersionCorrelationId=@VID and ParentRepositoryID=@PRID
and (CreateDate <= @CDate)
and ID not in (select top (@VKeep) ID from [Reporting].[Repository] nolock where VersionCorrelationId=@VID and ParentRepositoryID=@PRID
order by CreateDate Desc) and ID not in (select distinct ParentRepositoryID from [Reporting].[Repository] nolock )
fetch next from version_id into @VID, @PRID
end
close version_id
deallocate version_id
If @Delete = 0
begin
select COUNT(ID) as 'Total versions to be deleted' from @VPID
Select r2.Name as Folder, r.Name as Report,r.Description, COUNT(r.Name) as Versions from [Reporting].[Repository] r
Inner Join [Reporting].[Repository] r2 on r.ParentRepositoryID=r2.ID
where r.ID in (Select ID from @VPID)
group by r2.Name, r.name, r.Description
end
Else
begin
DECLARE reports CURSOR LOCAL STATIC READ_ONLY FOR SELECT ID FROM @VPID
OPEN reports
FETCH NEXT FROM reports INTO @ID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC [dbo].[#pRepositoryDelete] @ID
FETCH NEXT FROM reports INTO @ID
END
DEALLOCATE reports
select COUNT(ID) as 'Total Records Processed' from @VPID
end
--------------------------------------------------------------------------------
----------- Large table work; only run if deleting reports
If ((select top 1 [Delete] from #MRScriptVariables) = 1)
begin
BEGIN TRANSACTION;
SET XACT_ABORT ON;
IF EXISTS(select 1 from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'ReportColumnFilterOverride' AND CONSTRAINT_NAME = 'FK_ReportColumnFilterOverride_ColumnFilter')
ALTER TABLE [Reporting].[ReportColumnFilterOverride] DROP CONSTRAINT [FK_ReportColumnFilterOverride_ColumnFilter];
CREATE TABLE [Reporting].[tmp_ms_xx_ReportColumnFilter] (
[ReportID] INT NOT NULL,
[UnitIndex] INT NOT NULL,
[ColumnIndex] SMALLINT NOT NULL,
[BalanceFilterIndex] INT NULL,
[IsZero] BIT NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_ReportColumnFilter] PRIMARY KEY CLUSTERED ([ReportID] ASC, [UnitIndex] ASC, [ColumnIndex] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [Reporting].[ReportColumnFilter])
BEGIN
INSERT INTO [Reporting].[tmp_ms_xx_ReportColumnFilter] WITH (TABLOCK) ([ReportID], [UnitIndex], [ColumnIndex], [BalanceFilterIndex], [IsZero])
SELECT rcf.[ReportID],
rcf.[UnitIndex],
rcf.[ColumnIndex],
rcf.[BalanceFilterIndex],
rcf.[IsZero]
FROM [Reporting].[ReportColumnFilter] rcf
left outer join ##MRReportsToDelete r on (rcf.[ReportId] = r.[ReportID])
where r.[ReportId] IS NULL
ORDER BY [ReportID] ASC, [UnitIndex] ASC, [ColumnIndex] ASC;
END
DROP TABLE [Reporting].[ReportColumnFilter];
EXECUTE sp_rename N'[Reporting].[tmp_ms_xx_ReportColumnFilter]', N'ReportColumnFilter';
EXECUTE sp_rename N'[Reporting].tmp_ms_xx_constraint_PK_ReportColumnFilter', N'PK_ReportColumnFilter', N'OBJECT';
COMMIT TRANSACTION;
BEGIN TRANSACTION;
IF EXISTS(select 1 from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'ReportNotePlacement' AND CONSTRAINT_NAME = 'FK_ReportNotePlacement_LineTransaction')
alter table [Reporting].[ReportNotePlacement] drop constraint [FK_ReportNotePlacement_LineTransaction];
SET XACT_ABORT ON;
CREATE TABLE [Reporting].[tmp_ms_xx_ReportLineTransaction] (
[ReportID] INT NOT NULL,
[UnitIndex] INT NOT NULL,
[RowIndex] INT NOT NULL,
[LineAccountIndex] INT NOT NULL,
[Index] INT NOT NULL,
[LineType] TINYINT NOT NULL,
[IsPosted] BIT NOT NULL,
[IsOutOfBalance] BIT NOT NULL,
[Scenario] TINYINT NOT NULL,
[Year] SMALLINT NOT NULL,
[Period] SMALLINT NOT NULL,
[ApplyDate] DATETIME NULL,
[Description] NVARCHAR (1024) NOT NULL,
[CellData] IMAGE NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_ReportLineTransaction] PRIMARY KEY CLUSTERED ([ReportID] ASC, [UnitIndex] ASC, [RowIndex] ASC, [LineAccountIndex] ASC, [Index] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [Reporting].[ReportLineTransaction])
BEGIN
INSERT INTO [Reporting].[tmp_ms_xx_ReportLineTransaction] WITH (TABLOCK) ([ReportID], [UnitIndex], [RowIndex], [LineAccountIndex], [Index], [LineType], [IsPosted], [IsOutOfBalance], [Scenario], [Year], [Period], [ApplyDate], [Description], [CellData])
SELECT rlt.[ReportID],
rlt.[UnitIndex],
rlt.[RowIndex],
rlt.[LineAccountIndex],
rlt.[Index],
rlt.[LineType],
rlt.[IsPosted],
rlt.[IsOutOfBalance],
rlt.[Scenario],
rlt.[Year],
rlt.[Period],
rlt.[ApplyDate],
rlt.[Description],
rlt.[CellData]
FROM [Reporting].[ReportLineTransaction] rlt
left outer join ##MRReportsToDelete r on (rlt.[ReportId] = r.[ReportID])
where r.[ReportId] IS NULL
ORDER BY [ReportID] ASC, [UnitIndex] ASC, [RowIndex] ASC, [LineAccountIndex] ASC, [Index] ASC;
END
DROP TABLE [Reporting].[ReportLineTransaction];
EXECUTE sp_rename N'[Reporting].[tmp_ms_xx_ReportLineTransaction]', N'ReportLineTransaction';
EXECUTE sp_rename N'[Reporting].[tmp_ms_xx_constraint_PK_ReportLineTransaction]', N'PK_ReportLineTransaction', N'OBJECT';
COMMIT TRANSACTION;
BEGIN TRANSACTION;
SET XACT_ABORT ON;
IF EXISTS(select 1 from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'ReportNotePlacement' AND CONSTRAINT_NAME = 'FK_ReportNotePlacement_LineAccount')
alter table [Reporting].ReportNotePlacement drop constraint FK_ReportNotePlacement_LineAccount;
CREATE TABLE [Reporting].[tmp_ms_xx_ReportLineAccount] (
[ReportID] INT NOT NULL,
[UnitIndex] INT NOT NULL,
[RowIndex] INT NOT NULL,
[Index] INT NOT NULL,
[HasTransactionLines] BIT NOT NULL,
[IsPosted] BIT NOT NULL,
[IsOutOfBalance] BIT NOT NULL,
[AccountCode] NVARCHAR (1024) NOT NULL,
[Description] NVARCHAR (1024) NOT NULL,
[CellData] IMAGE NOT NULL,
[LineType] SMALLINT DEFAULT (0) NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_ReportLineAccount] PRIMARY KEY CLUSTERED ([ReportID] ASC, [UnitIndex] ASC, [RowIndex] ASC, [Index] ASC)
);
-------REPORTLINEAccount
IF EXISTS (SELECT TOP 1 1
FROM [Reporting].[ReportLineAccount])
BEGIN
INSERT INTO [Reporting].[tmp_ms_xx_ReportLineAccount] WITH (TABLOCK) ([ReportID], [UnitIndex], [RowIndex], [Index], [HasTransactionLines], [IsPosted], [IsOutOfBalance], [AccountCode], [Description], [CellData], [LineType])
SELECT rla.[ReportID],
rla.[UnitIndex],
rla.[RowIndex],
rla.[Index],
rla.[HasTransactionLines],
rla.[IsPosted],
rla.[IsOutOfBalance],
rla.[AccountCode],
rla.[Description],
rla.[CellData],
rla.[LineType]
FROM [Reporting].[ReportLineAccount] rla
left outer join ##MRReportsToDelete r on (rla.[ReportId] = r.[ReportID])
where r.[ReportId] IS NULL
ORDER BY [ReportID] ASC, [UnitIndex] ASC, [RowIndex] ASC, [Index] ASC;
END
DROP TABLE [Reporting].[ReportLineAccount];
EXECUTE sp_rename N'[Reporting].[tmp_ms_xx_ReportLineAccount]', N'ReportLineAccount';
EXECUTE sp_rename N'[Reporting].[tmp_ms_xx_constraint_PK_ReportLineAccount]', N'PK_ReportLineAccount', N'OBJECT';
COMMIT TRANSACTION;
-------REPORTLINEFINANCIAL
BEGIN TRANSACTION;
SET XACT_ABORT ON;
CREATE TABLE [Reporting].[tmp_ms_xx_ReportLineFinancial] (
[ReportID] INT NOT NULL,
[UnitIndex] INT NOT NULL,
[RowIndex] INT NOT NULL,
[HasAccountLines] BIT NOT NULL,
[IsZero] BIT NOT NULL,
[CellData] IMAGE NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_ReportLineFinancial] PRIMARY KEY CLUSTERED ([ReportID] ASC, [UnitIndex] ASC, [RowIndex] ASC)
);
IF EXISTS (SELECT TOP 1 1
FROM [Reporting].[ReportLineFinancial])
BEGIN
INSERT INTO [Reporting].[tmp_ms_xx_ReportLineFinancial] WITH (TABLOCK) ([ReportID], [UnitIndex], [RowIndex], [HasAccountLines], [IsZero], [CellData])
SELECT rlf.[ReportID],
rlf.[UnitIndex],
rlf.[RowIndex],
rlf.[HasAccountLines],
rlf.[IsZero],
rlf.[CellData]
FROM [Reporting].[ReportLineFinancial] rlf
left outer join ##MRReportsToDelete r on (rlf.[ReportId] = r.[ReportID])
where r.[ReportId] IS NULL
ORDER BY rlf.[ReportID] ASC, rlf.[UnitIndex] ASC, rlf.[RowIndex] ASC;
END
DROP TABLE [Reporting].[ReportLineFinancial];
EXECUTE sp_rename N'[Reporting].[tmp_ms_xx_ReportLineFinancial]', N'ReportLineFinancial';
EXECUTE sp_rename N'[Reporting].[tmp_ms_xx_constraint_PK_ReportLineFinancial]', N'PK_ReportLineFinancial', N'OBJECT';
COMMIT TRANSACTION;
----ReportColumnFilterOverride
BEGIN TRANSACTION;
SET XACT_ABORT ON;
CREATE TABLE [Reporting].[tmp_ms_xx_ReportColumnFilterOverride](
[ReportID] [int] NOT NULL,
[UnitIndex] [int] NOT NULL,
[ColumnIndex] [smallint] NOT NULL,
[RowIndex] [int] NOT NULL,
[BalanceFilterIndex] [int] NOT NULL,
CONSTRAINT [tmp_ms_xx_constraint_PK_ReportColumnFilterOverride] PRIMARY KEY CLUSTERED
(
[ReportID] ASC,
[UnitIndex] ASC,
[ColumnIndex] ASC,
[RowIndex] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
IF EXISTS (SELECT TOP 1 1
FROM [Reporting].[ReportColumnFilterOverride])
BEGIN
INSERT INTO [Reporting].[tmp_ms_xx_ReportColumnFilterOverride] WITH (TABLOCK) ([ReportID], [UnitIndex], [ColumnIndex], [RowIndex], [BalanceFilterIndex])
SELECT rcfo.[ReportID],
rcfo.[UnitIndex],
rcfo.[ColumnIndex],
rcfo.[RowIndex],
rcfo.[BalanceFilterIndex]
FROM [Reporting].[ReportColumnFilterOverride] rcfo
left outer join ##MRReportsToDelete r on (rcfo.[ReportId] = r.[ReportID])
where r.[ReportId] IS NULL
ORDER BY rcfo.[ReportID] ASC, rcfo.[UnitIndex] ASC, rcfo.[ColumnIndex] ASC, rcfo.[RowIndex] ASC;
END
DROP TABLE [Reporting].[ReportColumnFilterOverride];
ALTER TABLE [Reporting].[tmp_ms_xx_ReportColumnFilterOverride] WITH CHECK ADD CONSTRAINT [tmp_ms_xx_constraint_FK_ReportColumnFilterOverride_BalanceFilter] FOREIGN KEY([ReportID], [BalanceFilterIndex])
REFERENCES [Reporting].[ReportBalanceFilter] ([ReportID], [Index])
ALTER TABLE [Reporting].[tmp_ms_xx_ReportColumnFilterOverride] CHECK CONSTRAINT [tmp_ms_xx_constraint_FK_ReportColumnFilterOverride_BalanceFilter]
ALTER TABLE [Reporting].[tmp_ms_xx_ReportColumnFilterOverride] WITH CHECK ADD CONSTRAINT [tmp_ms_xx_constraint_FK_ReportColumnFilterOverride_Row] FOREIGN KEY([ReportID], [RowIndex])
REFERENCES [Reporting].[ReportRow] ([ReportID], [Index])
ALTER TABLE [Reporting].[tmp_ms_xx_ReportColumnFilterOverride] CHECK CONSTRAINT [tmp_ms_xx_constraint_FK_ReportColumnFilterOverride_Row]
EXECUTE sp_rename N'[Reporting].[tmp_ms_xx_ReportColumnFilterOverride]', N'ReportColumnFilterOverride';
EXECUTE sp_rename N'[Reporting].[tmp_ms_xx_constraint_PK_ReportColumnFilterOverride]', N'PK_ReportColumnFilterOverride', N'OBJECT';
EXECUTE sp_rename N'[Reporting].[tmp_ms_xx_constraint_FK_ReportColumnFilterOverride_BalanceFilter]', N'FK_ReportColumnFilterOverride_BalanceFilter', N'OBJECT';
EXECUTE sp_rename N'[Reporting].[tmp_ms_xx_constraint_FK_ReportColumnFilterOverride_Row]', N'FK_ReportColumnFilterOverride_Row', N'OBJECT';
---------APPLY MISSING FOREIGN KEYS/CONSTRAINTS
-----ReportColumnFilterOverride
ALTER TABLE [Reporting].[ReportColumnFilterOverride] WITH CHECK ADD CONSTRAINT [FK_ReportColumnFilterOverride_ColumnFilter] FOREIGN KEY([ReportID], [UnitIndex], [ColumnIndex])
REFERENCES [Reporting].[ReportColumnFilter] ([ReportID], [UnitIndex], [ColumnIndex])
ON UPDATE CASCADE
ON DELETE CASCADE
ALTER TABLE [Reporting].[ReportColumnFilterOverride] CHECK CONSTRAINT [FK_ReportColumnFilterOverride_ColumnFilter]
-----ReportLineFinancial
ALTER TABLE [Reporting].[ReportLineFinancial] WITH CHECK ADD CONSTRAINT [FK_ReportLineFinancial_Row] FOREIGN KEY([ReportID], [RowIndex])
REFERENCES [Reporting].[ReportRow] ([ReportID], [Index])
ALTER TABLE [Reporting].[ReportLineFinancial] CHECK CONSTRAINT [FK_ReportLineFinancial_Row]
ALTER TABLE [Reporting].[ReportLineFinancial] WITH CHECK ADD CONSTRAINT [FK_ReportLineFinancial_Unit] FOREIGN KEY([ReportID], [UnitIndex])
REFERENCES [Reporting].[ReportUnit] ([ReportID], [Index])
ON UPDATE CASCADE
ON DELETE CASCADE
ALTER TABLE [Reporting].[ReportLineFinancial] CHECK CONSTRAINT [FK_ReportLineFinancial_Unit]
-----ReportLineAccount
ALTER TABLE [Reporting].[ReportLineAccount] WITH CHECK ADD CONSTRAINT [CK_ReportLineAccount_Index] CHECK (([Index]>=(0)))
ALTER TABLE [Reporting].[ReportLineAccount] CHECK CONSTRAINT [CK_ReportLineAccount_Index]
ALTER TABLE [Reporting].[ReportLineAccount] WITH CHECK ADD CONSTRAINT [CK_ReportLineAccount_LineType] CHECK (([LineType]=(1) OR [LineType]=(0)))
ALTER TABLE [Reporting].[ReportLineAccount] CHECK CONSTRAINT [CK_ReportLineAccount_LineType]
ALTER TABLE [Reporting].[ReportLineAccount] WITH CHECK ADD CONSTRAINT [FK_ReportLineAccount_LineFinancial] FOREIGN KEY([ReportID], [UnitIndex], [RowIndex])
REFERENCES [Reporting].[ReportLineFinancial] ([ReportID], [UnitIndex], [RowIndex])
ON UPDATE CASCADE
ON DELETE CASCADE
ALTER TABLE [Reporting].[ReportLineAccount] CHECK CONSTRAINT [FK_ReportLineAccount_LineFinancial]
-----ReportLineTransaction
ALTER TABLE [Reporting].[ReportLineTransaction] WITH CHECK ADD CONSTRAINT [FK_ReportLineTransaction_ReportRow] FOREIGN KEY([ReportID], [RowIndex])
REFERENCES [Reporting].[ReportRow] ([ReportID], [Index])
ALTER TABLE [Reporting].[ReportLineTransaction] CHECK CONSTRAINT [FK_ReportLineTransaction_ReportRow]
ALTER TABLE [Reporting].[ReportLineTransaction] WITH CHECK ADD CONSTRAINT [FK_ReportLineTransaction_Unit] FOREIGN KEY([ReportID], [UnitIndex])
REFERENCES [Reporting].[ReportUnit] ([ReportID], [Index])
ON UPDATE CASCADE
ON DELETE CASCADE
ALTER TABLE [Reporting].[ReportLineTransaction] CHECK CONSTRAINT [FK_ReportLineTransaction_Unit]
ALTER TABLE [Reporting].[ReportLineTransaction] WITH CHECK ADD CONSTRAINT [CK_ReportLineTransaction_Index] CHECK (([Index]>=(0)))
ALTER TABLE [Reporting].[ReportLineTransaction] CHECK CONSTRAINT [CK_ReportLineTransaction_Index]
ALTER TABLE [Reporting].[ReportLineTransaction] WITH CHECK ADD CONSTRAINT [CK_ReportLineTransaction_LineType] CHECK (([LineType]=(33) OR [LineType]=(32) OR [LineType]=(31) OR [LineType]=(30) OR [LineType]=(20) OR [LineType]=(10) OR [LineType]=(0)))
ALTER TABLE [Reporting].[ReportLineTransaction] CHECK CONSTRAINT [CK_ReportLineTransaction_LineType]
ALTER TABLE [Reporting].[ReportLineTransaction] WITH CHECK ADD CONSTRAINT [CK_ReportLineTransaction_Period] CHECK (([Period]>=(0)))
ALTER TABLE [Reporting].[ReportLineTransaction] CHECK CONSTRAINT [CK_ReportLineTransaction_Period]
ALTER TABLE [Reporting].[ReportLineTransaction] WITH CHECK ADD CONSTRAINT [CK_ReportLineTransaction_Year] CHECK (([Year]>=(0)))
ALTER TABLE [Reporting].[ReportLineTransaction] CHECK CONSTRAINT [CK_ReportLineTransaction_Year]
-----ReportNotePlacement
ALTER TABLE [Reporting].[ReportNotePlacement] WITH CHECK ADD CONSTRAINT [FK_ReportNotePlacement_LineAccount] FOREIGN KEY([ReportID], [UnitIndex], [RowIndex], [LineAccountIndex])
REFERENCES [Reporting].[ReportLineAccount] ([ReportID], [UnitIndex], [RowIndex], [Index])
ALTER TABLE [Reporting].[ReportNotePlacement] CHECK CONSTRAINT [FK_ReportNotePlacement_LineAccount]
-----ReportColumnFilter
ALTER TABLE [Reporting].[ReportColumnFilter] WITH CHECK ADD CONSTRAINT [FK_ReportColumnFilter_BalanceFilter] FOREIGN KEY([ReportID], [BalanceFilterIndex])
REFERENCES [Reporting].[ReportBalanceFilter] ([ReportID], [Index])
ALTER TABLE [Reporting].[ReportColumnFilter] CHECK CONSTRAINT [FK_ReportColumnFilter_BalanceFilter]
ALTER TABLE [Reporting].[ReportColumnFilter] WITH CHECK ADD CONSTRAINT [FK_ReportColumnFilter_Column] FOREIGN KEY([ReportID], [ColumnIndex])
REFERENCES [Reporting].[ReportColumn] ([ReportID], [Index])
ALTER TABLE [Reporting].[ReportColumnFilter] CHECK CONSTRAINT [FK_ReportColumnFilter_Column]
ALTER TABLE [Reporting].[ReportColumnFilter] WITH CHECK ADD CONSTRAINT [FK_ReportColumnFilter_Unit] FOREIGN KEY([ReportID], [UnitIndex])
REFERENCES [Reporting].[ReportUnit] ([ReportID], [Index])
ON UPDATE CASCADE
ON DELETE CASCADE
ALTER TABLE [Reporting].[ReportColumnFilter] CHECK CONSTRAINT [FK_ReportColumnFilter_Unit]
COMMIT TRANSACTION;
--------Use ReportDelete for cascading deletes to clean up any other records
BEGIN TRANSACTION;
SET XACT_ABORT ON;
DECLARE @ReportID INT
DECLARE reports CURSOR LOCAL STATIC READ_ONLY FOR SELECT ReportId FROM ##MRReportsToDelete
OPEN reports
FETCH NEXT FROM reports INTO @ReportID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
PRINT @ReportID
EXEC [Reporting].[ReportDelete] @ReportID
FETCH NEXT FROM reports INTO @ReportID
END DEALLOCATE reports
COMMIT TRANSACTION;
END
