This will be a comparatively short post, but I feel this information is poorly documented and is hence worthwhile to mention here:
One of the problems faced in an office environment where SharePoint is deployed is that staff members do not always understand versioning and locking concepts – and even if they do, they may not always remember to or be able to check-in their documents. This quickly becomes an issue when large numbers of documents stay checked-out and the users locking the documents are unavailable or unaware of this.
Perhaps one of the most common ways of integrating a business system with a SharePoint document library is to author a custom XML web service that executes on the application server and references the SharePoint API (Microsoft.SharePoint.dll). In this context, the programmer has access to the Microsoft.SharePoint and Microsoft.SharePoint.WebControls namespaces.
So, in addressing the problem outlined earlier, a quick scan of the SharePoint object model would leave us with the impression that we could do the following:
SPWeb website = SPControl.GetContextWeb(Context); SPDocumentLibrary docLibrary = (SPDocumentLibrary)website.Lists["Shared Documents"]; foreach (SPCheckedOutFile coFile in docLibrary.CheckedOutFiles) { // return results }
However, the above will NOT produce the expected results. Looking closer at the documentation for the CheckedOutFiles property:
When someone creates a new file or adds a new file to a library that requires check-out, the file is initially checked out. The person who creates or adds the file must check it in before other people can use it. The CheckedOutFiles property returns a collection of SPCheckedOutFile objects with information about files that have been added to the library but have not been checked in.
(from MSDN)
This explanation is ambiguous at best, but it does provide an explanation as to why the above code sample does not work as intended. So, how then can we get a list of all documents checked-out across the entire document library? I have found two solutions to address this:
Solution 1: Custom View
The first solution is 100% server-side and will work on all types of SharePoint deployments. To begin, create a view on the document library, using the following options:
- Fields: Choose an appropriate subset that includes the ‘Checked Out To’ field.
- Filter: Include documents where ‘Checked Out To’ is not equal to an empty string (i.e. not null).
- Folders: Show all items without folders (this will expand the query over the entire library, regardless of folder).
The code snippet from earlier now changes to:
SPWeb website = SPControl.GetContextWeb(Context); SPDocumentLibrary docLibrary = (SPDocumentLibrary)website.Lists["Shared Documents"]; SPView checkedOutView = docLibrary.Views["Documents checked out"]; // name of the view foreach (SPListItem item in docLibrary.GetItems(checkedOutView)) { // return results }
The aforementioned view can be created programmatically, but requires knowledge of CAML.
Solution 2: Content Database Query
The second solution is one that I wouldn’t reccommend, but nevertheless executes faster than the technique outlined above. It can be either server-side or client-side, and only works on SharePoint deployments where SQL Server is used as the data provider (i.e. Web Farm mode). Furthermore, it requires knowledge of the following:
- Name of content database
- GUID of the list (document library)
You can then obtain a list of all checked-out documents in the document library by executing the following query on the content database:
SELECT DirName, LeafName, tp_Login FROM AllDocs d INNER JOIN UserInfo u ON d.LTCheckOutUserID=u.tp_ID WHERE ListId=@DocLibGUID AND IsCurrentVersion=1
While it definitely outperforms the earlier solution, it is a much less portable solution that is tied to a particular configuration and a particular version of SharePoint.
Final Words
In summary, the SharePoint API does not provide a built-in mechanism for globally determining which documents are checked out, despite giving us some false hope. The good news is that we can obtain this information with relative ease and low performance overheads. It can be very interesting to see just how many documents can be ‘left hanging’ by uninitiated users when a document management system is released into the wild, so having this knowledge can be invaluable for developers and admins alike.
Thank you so much for this tip. I had only been going to the Shared Documents settings to view the documents that currently do not have a checked in version. This will really help in managing my groups’ files! Thanks.
Thanks for this article. It was driving me crazy as to filtering for IS NOT NULL. I never would have thought to use >”