Extracting data from Sharepoint Version table

When using SharePoint 2007, if we turn the Version Control settings to On for a List/Library, then different versions of the file or listitem get stored in the Content DB.

There are multiple ways to retrieve this information:

1. Using MOSS 2007 API
2. Via Versions.asmx - An OOB SharePoint Webservice
3. Or Querying the Docs & DocVersions table directly in using T-SQL statements

Now through SP API, you can try something like this -->
SPWeb web = new SPSite("your site url").OpenWeb();


SPList lib = web.Lists["your library"];


foreach (SPListItem item in lib.Items)


{


SPFile file=item.File


SPFileVersionCollection versions = file.Versions;


//Add your code to loop through the version collection & add custom logic here..


}


Coming to WebService approach:


SharePoint has a Versions.asmx web service to work with versions in document libraries.


For introduction, check out how to utilize this to get some information :
http://www.c-sharpcorner.com/UploadFile/klaus_salchner@hotmail.com/SharePointWS11152005045049AM/SharePointWS.aspx




Incase, you want to go ahead querying the SQL tables directly, then you could try a query as below:


SELECT DocVersions.version
FROM Docs INNER JOIN DocVersions ON DocVersions.Id = Docs.Id


Although it is not recommended that you query the SP DB directly. Any modifications or changes to the Content DB tables in unsupported.




Also the data is stored in UTF-8 Encoded format. After the query runs, it returns some data like this:
UIVersion




512
1024
1536
2048
512
1024
512


The above data is difficult to interpret in terms of Version numbers. Anyway, just to let you know


512 = version 1,
1024 =version 2,
1025 =version2.1,
2048= version 3,
1=version 0.1
and so on..


I guess the pattern is clear to you. Hope this article helps you.