219 lines
8.1 KiB
Transact-SQL
219 lines
8.1 KiB
Transact-SQL
--********************************************************************
|
|
--********************************************************************
|
|
-- REMOVE THIS PRIOR TO SENDING TO BARAKAH
|
|
--********************************************************************
|
|
--********************************************************************
|
|
ALTER DATABASE VEPROMS_Barakah
|
|
SET RECOVERY SIMPLE
|
|
GO
|
|
DBCC SHRINKFILE (VEPROMS_Users_log, 1)
|
|
GO
|
|
ALTER DATABASE VEPROMS_Barakah
|
|
SET RECOVERY FULL
|
|
--********************************************************************
|
|
--********************************************************************
|
|
|
|
|
|
-----------------------------------------------
|
|
-- Remove the Foreign Key constraint from
|
|
-- tblItems so the we can accurately remove
|
|
-- any deleted parent items (re-added later)
|
|
-----------------------------------------------
|
|
ALTER TABLE [dbo].[tblItems] DROP CONSTRAINT [FK_Items_Items]
|
|
GO
|
|
--------------------------------------------------
|
|
-- Get the initial size report for the database
|
|
--------------------------------------------------
|
|
SELECT
|
|
t.name AS TableName,
|
|
p.rows As TotalRows,
|
|
CAST(ROUND((((SUM(a.total_pages) * 8) / 1024.00) / 1024.0), 3) AS NUMERIC(36, 2)) AS TotalSpaceGB
|
|
FROM
|
|
sys.tables t
|
|
INNER JOIN
|
|
sys.indexes i ON t.object_id = i.object_id
|
|
INNER JOIN
|
|
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
|
|
INNER JOIN
|
|
sys.allocation_units a ON p.partition_id = a.container_id
|
|
LEFT OUTER JOIN
|
|
sys.schemas s ON t.schema_id = s.schema_id
|
|
WHERE
|
|
t.name NOT LIKE 'dt%'
|
|
AND t.is_ms_shipped = 0
|
|
AND i.object_id > 255
|
|
GROUP BY
|
|
t.name, s.name, p.rows
|
|
ORDER BY
|
|
TotalSpaceGB DESC, t.name
|
|
|
|
|
|
------------------------------------------------------------------------
|
|
-- Create the temp tables for ItemId, ContentId and Error handling
|
|
------------------------------------------------------------------------
|
|
IF OBJECT_ID('tempdb..#TmpItemId') IS NOT NULL
|
|
DROP TABLE #TmpItemId
|
|
|
|
SELECT DISTINCT ItemId INTO #TmpItemId FROM tblItems WHERE DeleteStatus != 0 ORDER BY ItemID
|
|
|
|
IF OBJECT_ID('tempdb..#TmpContentId') IS NOT NULL
|
|
DROP TABLE #TmpContentId
|
|
|
|
SELECT DISTINCT ContentID INTO #TmpContentId FROM tblContents WHERE DeleteStatus != 0 ORDER BY ContentID
|
|
|
|
IF OBJECT_ID('tempdb..#TmpErrors') IS NOT NULL
|
|
DROP TABLE #TmpErrors
|
|
|
|
CREATE TABLE #TmpErrors
|
|
(
|
|
DeleteStatus int
|
|
);
|
|
---------------------------
|
|
--PHASE 1 DELETIONS
|
|
---------------------------
|
|
DELETE FROM Figures where ROFSTID not in(select distinct rofstid from Associations)
|
|
DELETE FROM ROFSTs where ROFSTID not in(select distinct rofstid from Associations)
|
|
DELETE FROM roimages where rodbid not in(select distinct rodbid from rofsts)
|
|
DELETE FROM documents where docid not in(select distinct docid from entries)
|
|
|
|
-------------------------------------
|
|
-- CURSOR DECLARATION AND SETUP
|
|
-------------------------------------
|
|
DECLARE @deleteStatus int
|
|
DECLARE procSet_cursor CURSOR FOR
|
|
|
|
--Get the list of deleted procedures
|
|
SELECT DeleteStatus FROM tblContents WHERE Type = 0 AND DeleteStatus != 0 ORDER BY DeleteStatus
|
|
|
|
OPEN procSet_cursor
|
|
FETCH NEXT FROM procSet_cursor INTO @deleteStatus
|
|
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
|
|
BEGIN TRY
|
|
PRINT '********************************************************************'
|
|
PRINT @deleteStatus
|
|
PRINT '********************************************************************'
|
|
|
|
---------------------------
|
|
--PHASE 2 DELETIONS
|
|
---------------------------
|
|
DELETE FROM tblAnnotations WHERE Deletestatus !=0
|
|
DELETE FROM drousages where docid in(select docid from tbldocuments WHERE DeleteStatus = @deleteStatus)
|
|
DELETE FROM tblEntries WHERE Deletestatus !=0 OR ContentID IN (SELECT * FROM #TmpContentId)
|
|
DELETE FROM tblDocuments WHERE DeleteStatus = @deleteStatus
|
|
DELETE FROM tblGrids WHERE DeleteStatus = @deleteStatus
|
|
DELETE FROM tblImages WHERE DeleteStatus = @deleteStatus
|
|
---------------------------
|
|
--PHASE 3 ITEM DELETIONS
|
|
---------------------------
|
|
DELETE FROM tblROUsages WHERE DeleteStatus = @deleteStatus OR ContentID IN (SELECT * FROM #TmpContentId)
|
|
DELETE FROM tblTransitions WHERE Deletestatus !=0 OR DeleteStatus = @deleteStatus OR RangeID IN (SELECT * FROM #TmpItemId) OR ToID IN (SELECT * FROM #TmpItemId)
|
|
DELETE FROM tblParts WHERE Deletestatus !=0 OR DeleteStatus = @deleteStatus OR Itemid IN (SELECT * FROM #TmpItemId) OR ContentID IN (SELECT * FROM #TmpContentId)
|
|
PRINT ''
|
|
PRINT '*****************'
|
|
PRINT 'Deleting Items'
|
|
PRINT '*****************';
|
|
DELETE FROM tblItems WHERE PreviousID IS NOT NULL AND (DeleteStatus = @deleteStatus AND PreviousID IN (SELECT * FROM #TmpItemId))
|
|
DELETE FROM tblItems WHERE ItemId IN (SELECT * FROM #TmpItemId)
|
|
|
|
---------------------------
|
|
--CONTENT DELETIONS
|
|
---------------------------
|
|
PRINT ''
|
|
PRINT '*****************'
|
|
PRINT 'Deleting Contents'
|
|
PRINT '*****************';
|
|
DELETE FROM tblContents WHERE DeleteStatus = @deleteStatus AND ContentID IN (SELECT * FROM #TmpContentId)
|
|
|
|
---------------------------
|
|
--AUDIT DELETIONS
|
|
---------------------------
|
|
PRINT ''
|
|
PRINT 'Deleting Audits'
|
|
DELETE FROM AnnotationAudits WHERE DeleteStatus = @deleteStatus
|
|
DELETE FROM ContentAudits WHERE DeleteStatus = @deleteStatus
|
|
DELETE FROM EntryAudits WHERE DeleteStatus = @deleteStatus
|
|
DELETE FROM DocumentAudits WHERE DeleteStatus = @deleteStatus
|
|
DELETE FROM GridAudits WHERE DeleteStatus = @deleteStatus
|
|
DELETE FROM ImageAudits WHERE DeleteStatus = @deleteStatus
|
|
DELETE FROM ItemAudits WHERE DeleteStatus = @deleteStatus
|
|
DELETE FROM PartAudits WHERE DeleteStatus = @deleteStatus
|
|
DELETE FROM ROUsageAudits WHERE DeleteStatus = @deleteStatus
|
|
DELETE FROM TransitionAudits WHERE DeleteStatus = @deleteStatus
|
|
|
|
END TRY
|
|
BEGIN CATCH
|
|
SELECT @deleteStatus AS DeleteStatus, ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;
|
|
INSERT INTO #TmpErrors VALUES(@deleteStatus)
|
|
END CATCH
|
|
|
|
FETCH NEXT FROM procSet_cursor INTO @deleteStatus
|
|
END
|
|
|
|
CLOSE procSet_cursor
|
|
DEALLOCATE procSet_cursor
|
|
|
|
------------------------------------------------
|
|
-- Remove any orphaned records from tblItems
|
|
-- NOTE - These items are a result of the item
|
|
-- in question not being marked at deleted when
|
|
-- the parent item was deleted
|
|
------------------------------------------------
|
|
DECLARE @recordCount int
|
|
SET @recordCount = (SELECT COUNT(*) FROM tblItems WHERE PreviousID IN (SELECT PreviousID FROM tblItems WHERE PreviousId NOT IN (Select ItemId FROM tblItems)))
|
|
|
|
WHILE @recordCount > 0
|
|
BEGIN
|
|
DELETE FROM tblItems WHERE PreviousID IN (SELECT PreviousID FROM tblItems WHERE PreviousId NOT IN (Select ItemId FROM tblItems))
|
|
SET @recordCount = (SELECT COUNT(*) FROM tblItems WHERE PreviousID IN (SELECT PreviousID FROM tblItems WHERE PreviousId NOT IN (Select ItemId FROM tblItems)))
|
|
END
|
|
PRINT str(@recordCount) + ' orphaned records deleted'
|
|
|
|
-----------------------------------------------------
|
|
-- Re-add the Foreign Key constraint for tblItems
|
|
-----------------------------------------------------
|
|
ALTER TABLE [dbo].[tblItems] WITH CHECK ADD CONSTRAINT [FK_Items_Items] FOREIGN KEY([PreviousID])
|
|
REFERENCES [dbo].[tblItems] ([ItemID])
|
|
GO
|
|
|
|
ALTER TABLE [dbo].[tblItems] CHECK CONSTRAINT [FK_Items_Items]
|
|
GO
|
|
|
|
-----------------------------------------------------
|
|
-- Get the final size report for the database size
|
|
-----------------------------------------------------
|
|
SELECT
|
|
t.name AS TableName,
|
|
p.rows As TotalRows,
|
|
CAST(ROUND((((SUM(a.total_pages) * 8) / 1024.00) / 1024.0), 3) AS NUMERIC(36, 2)) AS TotalSpaceGB
|
|
FROM
|
|
sys.tables t
|
|
INNER JOIN
|
|
sys.indexes i ON t.object_id = i.object_id
|
|
INNER JOIN
|
|
sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
|
|
INNER JOIN
|
|
sys.allocation_units a ON p.partition_id = a.container_id
|
|
LEFT OUTER JOIN
|
|
sys.schemas s ON t.schema_id = s.schema_id
|
|
WHERE
|
|
t.name NOT LIKE 'dt%'
|
|
AND t.is_ms_shipped = 0
|
|
AND i.object_id > 255
|
|
GROUP BY
|
|
t.name, s.name, p.rows
|
|
ORDER BY
|
|
TotalSpaceGB DESC, t.name
|
|
|
|
--Show any errors recorded in the #TmpErrors table
|
|
SELECT * FROM #TmpErrors
|
|
|
|
-----------------------------
|
|
-- Clean up the temp tables
|
|
-----------------------------
|
|
DROP TABLE #TmpItemId
|
|
DROP TABLE #TmpContentId
|
|
DROP TABLE #TmpErrors
|