Query SharePoint List to get all document versions information: SharePoint 2007

Two days back I have posted an article on SharePoint Storage Problems, which can give you a fair idea of what things to look at when you plan or your SharePoint site has eaten space more than expected.

While looking at the same problem, we already know that SharePoint document versions can be the one of the element of sudden spike in SharePoint Storage but how we could determine how many versions are there on the SharePoint collection and what size they are off?

As you all know SQL is the ocean for the SharePoint Information storage, so SQL can provide us the relevant information about this. I wrote a SQL query to get the information on the versioning of all the document in the particular site.

Select a.version, a.timecreated, a.size, b.dirname,b.leafname, b.listid

from dbo.DocVersions a, alldocs b  where a.siteid=b.siteid

and b.dirname = 'sites/AMAR' and a.size 'greator than and equal to' (<>);0 and b.listid is not null

order by a.size

In the above query, columns refer to:

Versions: refer to number of versions of documents in a list

Timecreated: time when the document was created

Size: is the size of the versions (in KB)

Dirname: site name (e.g. sites/amar)

List id: ID of the list in the SharePoint Site.

Hope this piece of information does help you. If you have any questions and concerns do let me know.