Richard, here is the script. Reviewing it again, there is really just a single parameter, which is the number of versions you wish to keep. The second parameter is simply a designation to either print a list of what will be deleted or to actually execute the delete statement. These are on lines 14 & 15. These two lines are the only ones (aside from the db designation) where you would need to edit anything prior to running.
use [TWO]
declare @iCursorError int,
@VID varchar(255),
@ID varchar(255),
@RCount int,
@VKeep int,
@Delete bit
select @iCursorError = 0,
@VID = '',
@ID = '',
@RCount = 0,
@VKeep = 10, -- Number of versions to keep
@Delete = 0 -- Set to 1 to delete versions or 0 to just print versions that will delete
declare version_id cursor for
select distinct VersionID
from Repository (nolock)
where type=10
open version_id
fetch next from version_id into @VID
while (@@FETCH_STATUS = 0)
begin
declare delete_report cursor for
select ID from Repository where VersionID=@VID
and ID not in (select top (@VKeep) ID from Repository where VersionID=@VID order by CreateDate Desc)
open delete_report
fetch next from delete_report into @ID
while (@@FETCH_STATUS = 0)
begin
select * from Repository where ID=@ID
set @RCount = @RCount + 1
If (@Delete=1)
begin
delete Report where RepositoryID =@ID
delete Repository where ID =@ID
end
fetch next from delete_report into @ID
end
close delete_report
deallocate delete_report
fetch next from version_id into @VID
end
close version_id
deallocate version_id
select @RCount as 'Total Records Processed'
select @VID = ''
