Stored procedure that will help us to find itemwise max version in Sitecore

Hi All,

Below stored procedure will help us to find itemwise max version in Sitecore. For better performance, we can delete older versions of any item and we can keep latest 7 – 10 versions of any item. Execute the below Stored Procedure in Master database and you will find itemwise max version of items.

create procedure Stp_Version_count

@counter          int 

as

begin

      WITH [Content_Items] AS  

       ( 

        SELECT [ID], [Name], Created 

         FROM [dbo].[Items] 

         WHERE ID='{11111111-1111-1111-1111-111111111111}' 

        

         UNION ALL 

        

         SELECT  i.[ID], i.[Name], i.Created 

         FROM [dbo].[Items] i 

         INNER JOIN [Content_Items] ci ON ci.ID = i.[ParentID] 

       ) 

       

       select ID,[Name], Created into #tbl    

       FROM [Content_Items]

       

       select top (@counter) ItemId,[Name],max(Version) as Total_No_Of_version from #tbl a ,

       VersionedFields b where a.ID = b.ItemId group by ItemId,[Name] order by max(Version) desc

      drop table #tbl

 end

   

exec Stp_Version_count 100

drop procedure Stp_Version_count