78 lines
3.8 KiB
Transact-SQL
78 lines
3.8 KiB
Transact-SQL
|
|
|
|
declare @CleanUp Table
|
|
(
|
|
ItemID int primary key,
|
|
ContentID int
|
|
)
|
|
|
|
Insert Into @Cleanup
|
|
select ItemID, ContentID from (
|
|
select ppitemid from (
|
|
select pp.itemid ppitemid, cast(Config as xml) xConfig from vefn_getVersionItems('') VI
|
|
join vefn_getversionnames() VN ON vn.versionid =vi.versionid
|
|
join contents cc ON cc.ContentID = VI.ContentID
|
|
join parts pp on pp.contentID = vi.ContentID and FromType = 6
|
|
Where vi.ContentID in(Select ContentID from Parts where FromType = 2)) AH
|
|
outer apply xConfig.nodes('//SubSection') tSubSection(xSubSection)
|
|
where IsNull(xSubSection.value('@Edit','varchar(1)'),'N') = 'N') AH
|
|
Cross Apply vefn_SiblingChildrenItems(ppItemID)
|
|
|
|
--select itemid, contentid, vn.foldername,dbo.ve_getshortpath(itemid) location from vefn_getVersionItems('') VI
|
|
--join vefn_getversionnames() VN ON vn.versionid =vi.versionid
|
|
--Where ContentID in(Select ContentID from Parts where FromType = 2) -- Sections
|
|
--And ContentID in(Select ContentID from Parts where FromType = 6) -- Steps
|
|
|
|
|
|
--DocVersions ItemID Should not expect to see any
|
|
--Revisions ItemID Should not expect to see any
|
|
--Owners OwnerItemID Should not expect to see any
|
|
PRINT 'tblAnnotations ItemID Delete'
|
|
delete from tblAnnotations where itemid in(select itemid from @CleanUp)
|
|
PRINT 'AnnotationAudits ItemID Delete'
|
|
delete from AnnotationAudits where itemid in(select itemid from @CleanUp)
|
|
PRINT 'tblParts ItemID ContentID Delete'
|
|
delete from tblParts where itemid in(select itemid from @CleanUp)
|
|
PRINT 'PartAudits ItemID ContentID Delete'
|
|
delete from PartAudits where itemid in(select itemid from @CleanUp)
|
|
delete from zTransitions where transitionid in (select transitionid from transitions where fromid in (select contentid from @CleanUp))
|
|
PRINT 'tblTransitions FromID Delete'
|
|
delete from tblTransitions where fromid in(select contentid from @CleanUp)
|
|
PRINT 'TransitionAudits FromID Delete'
|
|
delete from TransitionAudits where fromid in(select contentid from @CleanUp)
|
|
select * from transitions where toid in(select itemid from @Cleanup)
|
|
|
|
PRINT 'tblItems ItemID ContentID Delete (PreviousID)'
|
|
update tblitems set previousID = null where itemid in(select itemid from @CleanUp)
|
|
delete from tblItems where itemid in(select itemid from @CleanUp)
|
|
PRINT 'ItemAudits ItemID ContentID Delete'
|
|
update ItemAudits set previousID = null where itemid in(select itemid from @CleanUp)
|
|
delete from ItemAudits where itemid in(select itemid from @CleanUp)
|
|
PRINT 'tblEntries ContentID Delete'
|
|
delete from tblEntries where contentid in(select contentid from @CleanUp)
|
|
PRINT 'EntryAudits ContentID Delete'
|
|
delete from EntryAudits where contentid in(select contentid from @CleanUp)
|
|
PRINT 'tblROUsages ContentID Delete'
|
|
delete from tblROUsages where contentid in(select contentid from @CleanUp)
|
|
PRINT 'ROUsageAudits ContentID Delete'
|
|
delete from ROUsageAudits where contentid in(select contentid from @CleanUp)
|
|
PRINT 'tblGrids ContentID Delete'
|
|
delete from tblGrids where contentid in(select contentid from @CleanUp)
|
|
PRINT 'GridAudits ContentID Delete'
|
|
delete from GridAudits where contentid in(select contentid from @CleanUp)
|
|
PRINT 'tblImages ContentID Delete'
|
|
delete from tblImages where contentid in(select contentid from @CleanUp)
|
|
PRINT 'ImageAudits ContentID Delete'
|
|
delete from ImageAudits where contentid in(select contentid from @CleanUp)
|
|
PRINT 'Details ContentID Delete'
|
|
delete from Details where contentid in(select contentid from @CleanUp)
|
|
PRINT 'ZContents ContentID Delete'
|
|
delete from ZContents where contentid in(select contentid from @CleanUp)
|
|
PRINT 'tblContents ContentID Delete'
|
|
delete from tblContents where contentid in(select contentid from @CleanUp)
|
|
PRINT 'ContentAudits ContentID Delete'
|
|
delete from ContentAudits where contentid in(select contentid from @CleanUp)
|
|
PRINT 'Cleanup Complete'
|
|
|
|
|