I ran the following powershell however, it only list 20 items and in reality I have about 50 items for the site I am running for. I am the farm and site admin. I see 50 files when I go to the library setting manage checked out files http://inside.nv.ed/teams/mkt/_layouts/ManageCheckedOutFiles.aspx?List=%7BBF36234B%2D0303%2D470D%2D887C%2D3A4724FFCB58%7D. Any idea why there is a discrepancy?

Add-PSSnapin Microsoft.SharePoint.PowerShell

$spWeb = Get-SPWeb "http://inside.nv.ed/teams/mkt"
foreach ($list in ($spWeb.Lists | ? {$_ -is [Microsoft.SharePoint.SPDocumentLibrary]})) {
    #Write-Host "Scanning List: $($list.RootFolder.ServerRelativeUrl)"
    $CAML = "<Where><Geq><FieldRef Name='CheckoutUser' LookupId='TRUE'/><Value Type='int'>0</Value></Geq></Where>"
    #$CAML = "<where><isnull><fieldref lookupid='TRUE' name='CheckoutUser'></fieldref></isnull></where>"
    $query = new-object Microsoft.SharePoint.SPQuery
    $query.ViewAttributes = "Scope='Recursive'" 
    $query.Query = $CAML
    $listitems = $list.GetItems($query)
    #write-host $listitems.Count
    foreach ($item in $listitems)
        #write-host $item.url
        write-host $fullURL

  • if !$item.Url.EndsWith(".aspx")) { continue } Could it be that you are only looking for specific files?
    – MickB
    Commented May 11, 2016 at 19:40
  • I took out the aspx line and remodified the entire code. Commented May 11, 2016 at 19:42
  • Looks like the mystery is lying when there are required columns in the library and they are not filled. networkworld.com/article/2346678/microsoft-subnet/… So even with this scenario what tweak I need to make to powershell to get all the items Commented May 11, 2016 at 20:15
  • Looks like you will want to search for both files that have a checked in version, and files that have never been checked in.
    – MickB
    Commented May 12, 2016 at 15:14

1 Answer 1


Either it's a bug or by design by Microsoft. We are in the process of migration and we have received tons of complaints that their files are missing. I have no choice but hit the sql db directly. here is the sql query that worked like I intended. I am NOT liable if your record(s) locks up due to this query. Use at your own risk.

1. Go to the database and open an new sql query window (from sql management)
2. Execute following query
  FROM [WSS_Content_NOV].[dbo].[AllDocs]
  where SiteId='E2FF98A7-B719-428D-8C30-856F08989691' and 
  CheckoutUserId is not null
3. Export the output to excel or csv for department review

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.