SharePoint Tip #34. Do you know “how to get the list of check-out files"?!
SharePoint provides OOTB support for document versioning, allowing user to check-in/check-out files, and keep history of changed documents. But unfortunately, versioning system doesn't provide inbuilt method to find these all check-out items made by users.
For some administrative task and farm maintenance it's recommended to have all files to be check-in before making changes. And this task became daunting task for administrators, because you need to check all site collection and list collections manually via SharePoint interface , to check-in user files.
The way to get the list of all check-out files across specific site is to use the following SQL script for SharePoint AdminContent DB
1: SELECT tp_DirName, 'http://SiteName/' + tp_DirName AS Expr1, tp_LeafName,
2: CASE WHEN AllUserData.tp_ContentType = 'Item' THEN 'http://SiteName/' + AllUserData.tp_DirName + '/DispForm.aspx?ID=' + AllUserData.tp_LeafName ELSE 'http://SiteName/'
3: + AllUserData.tp_DirName + '/' + AllUserData.tp_LeafName END AS Link, tp_ContentType, nvarchar1, nvarchar2, tp_ModerationStatus, tp_DeleteTransactionId,
4: tp_IsCurrent
5: FROM AllUserData AS AllUserData
6: WHERE (tp_ModerationStatus = 2) AND (tp_DeleteTransactionId = 0x0) AND (tp_IsCurrent = 1)
7: ORDER BY tp_DirName, tp_LeafName
Source
Have anything to add?! Send your tips to be published via this form.