Version History in SharePoint via SQL

Posted by on February 21, 2012 in Nintex, SharePoint 2010, SQL | 0 comments

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

Incoming search terms:

  • where is dbo alldocversions located in SQL
  • sharepoint alldocversions
  • dbo alldocversions
  • AllDocVersions sharepoint
  • sharepoint 2010 accessing version history
  • sharepoint 2010 alldocversions
  • sharepoint alldocversions uiversion
  • sharepoint 2010 view version history sql
  • sharepoint alldocs and alldocversions
  • sharepoint 2010 query version history with access