Revisited – Content Deployment error? How to find those checked out items within Sharepoint

A month or so ago I put up a post about how to find checked out items within a Sharepoint site.  This particular error tends to bring up problems with Content Deployment, as CD cannot occur if there are any items checked out.

Unfortunately there is no inbuilt method to find these items for an Administrator, the only way within the standard Sharepoint interface if that on the Site Actions menu, each user can check if there are any items still checked out and quickly check them back in.  Training users to do this can be problematic however, and sometimes it is just easier to point them at the items that are still checked out.

I had a query to do this, but one of the readers has suggested a much better query than my own for finding these items :

SELECT AllUserData.tp_DirName, 'http://SiteName/' + AllUserData.tp_DirName, AllUserData.tp_LeafName,
case when AllUserData.tp_ContentType = 'Item'
then 'http://SiteName/' + AllUserData.tp_DirName + '/DispForm.aspx?ID=' + AllUserData.tp_LeafName
else 'http://SiteName/' + AllUserData.tp_DirName + '/' + AllUserData.tp_LeafName
end as Link,AllUserData.tp_ContentType, AllUserData.nvarchar1, AllUserData.nvarchar2, AllUserData.tp_ModerationStatus, AllUserData.tp_DeleteTransactionId, AllUserData.tp_IsCurrent
FROM AllUserData AllUserData
WHERE (AllUserData.tp_ModerationStatus=2)
AND (AllUserData.tp_DeleteTransactionId=0x0)
AND (AllUserData.tp_IsCurrent=1)
ORDER BY AllUserData.tp_DirName, AllUserData.tp_LeafName

Just replace the http://SiteName/ with the url to your Site, and use this on the database of your Web Application and you are good to go. Thank you very much to rwoods@city.pg.bc.ca for supplying this great query.