Listing all checked out files

Question

As a site administrator I have to list all checked out files in the site collection. Looking at the DB is looks like the tp_CheckoutUserId field in AllUserData might be a key to this query.

Is it possible to get this information through the web services or will I have to use the object model?

Update: It seems like the customer would like to be able to list just those files that have been checked out (not necessary modified) by a specific user. Unless the query takes several minutes it should be a realtime query, but the site collection consist of +10 000 web with 3 document libraries on each web so performance might be an issue.

Answer 1

Depending on what you are trying to do there are a few approaches to this:

-- Use the Manage Content and Structure Reports as per http://blogs.syrinx.com/blogs/sharepoint/archive/2008/04/14/content-and-structure-reports-just-a-caml-ride-away.aspx but this is fairly limited as it doesn't let you specify who checked out the document and on which site.

-- Use the object model - this gives you more flexibility but requires the code to be running on the SharePoint server. To do this you could use a query such as the following:

using (SPWeb web = new SPSite("http://intranet/").OpenWeb())
{
    SPSiteDataQuery q = new SPSiteDataQuery();
    q.Lists = "<Lists BaseType='1'/>";
    q.Query = "<Where><Geq><FieldRef Name='CheckoutUser' LookupId='TRUE'/><Value Type='int'>0</Value></Geq></Where>";
    q.Webs = "<Webs Scope='SiteCollection' />";
    q.ViewFields = "<FieldRef Name='Title' /><FieldRef Name='ID' />";
    q.RowLimit = 10;
}

-- Use the web services to allow you to run the code remotely. This would likely require more effort but should be achievable using the Query web service as long as you expose the CheckoutUser field as a managed property in the search settings for the SSP.

Answer 2

One way you could do, but I'm not sure how well recommended it would be, is to use the object model to recursively iterate over your site collection and document libraries to collect a list of all documents that are checked out.

The reason for my unsure of how well recommended this approach would be is that it would be very inefficient and slow I think, depending on your code practice. For a one-off tool to run every now and again it could work ok though.

Answer 3

I figured out a query (for SP2007). Please note that Microsoft doesn't allow executing queries, so use on your own risk. This is not an answer to the question, but I believe worth sharing. Hope it helps others. The query retrieves some data from the Config database. You might need to change the name of the database in case it differs in you environment.

SELECT [AllUserData].[tp_ID]
  ,SharePoint_Config.dbo.SiteMap.Path + [AllUserData].tp_DirName + '/' + [AllUserData].tp_LeafName as url
  ,UserInfo.tp_Login
FROM [AllUserData] INNER JOIN UserInfo ON AllUserData.tp_CheckoutUserId = UserInfo.tp_ID AND AllUserData.tp_SiteId = UserInfo.tp_SiteID INNER JOIN SharePoint_Config.dbo.SiteMap ON AllUserData.tp_SiteId = SharePoint_Config.dbo.SiteMap.Id
WHERE [AllUserData].tp_IsCurrent = 1 AND [AllUserData].tp_CheckoutUserId is not null
ORDER BY url

Answer 4

@James Love: we have implemented a tool that uses the Sharepoit Client Object Model for generating Excel Report of all checked out files within a site (incl. sub-sites and their libraries). It works well. We are using CAML for our quries, so you have just one query per library.

You can try it: http://dms-shuttle.com/documentation/export-list-checked-out-files-sharepoint/