Version History in SharePoint via SQL
Recently I posted about how to get check-in comments with Nintex via MS SQL – turns out there was a bit more complexity involved in the structure of the version history then first thought (surprise surprise). Below is the stored procedure created to reliably extract the highest MAJOR version of a SharePoint document. So, if a document is currently v5.4 in your SharePoint library, this will grab the 5.0 version:
USE [MySharePoint_Content_DB] GO /****** Object: StoredProcedure [dbo].[proc_GetDocVersion] Script Date: 02/17/2012 13:37:36 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[proc_GetDocVersion]( @LeafName nvarchar(260) ) AS SET NOCOUNT ON SELECT TOP 1 x.UIVersion FROM ( SELECT AllDocVersions.UIVersion AS UIVersion FROM AllDocVersions JOIN AllDocs ON AllDocs.[ID]= AllDocVersions.[ID] WHERE AllDocs.LeafName = @LeafName AND ((CONVERT([nvarchar],AllDocVersions.UIVersion/(512),0)+'.')+ CONVERT([nvarchar],AllDocVersions.UIVersion%(512),0)) LIKE '%.0' UNION ALL SELECT AllDocs.UIVersion As UIVersion FROM AllDocs WHERE AllDocs.LeafName = @LeafName AND ((CONVERT([nvarchar],AllDocs.UIVersion/(512),0)+'.')+ CONVERT([nvarchar],AllDocs.UIVersion%(512),0)) LIKE '%.0' ) x ORDER BY UIVersion Desc;
Accessing the SQL DB in SharePoint 2010 directly (as opposed to using the SharePoint API’s etc.) is generally considered a cowboy maneuver and can get in you in lot’s of trouble with inconsistent results as well as performance hits. Use this SQL at your own risk, if not as just a means to better understand the plumbing that goes on in the basement of SharePoint.
Additionally, note that if you are accessing version history via the /vti_history// method, there are some major caveats as described in the following (note it’s referring to SP 2007 which uses single-digit version numbers but the description of the potential run-on situation still applies): http://blogs.msdn.com/b/roberthorvick/archive/2007/01/04/wss-rant-linking-to-the-latest-version-of-a-sharepoint-document-considered-harmful-lessons-6-and-7.aspx