How do I use a SQL query to check-in a document?

Question

Yesterday I started looking at the tables in my SharePoint 2010's Content databases for the first time and I noticed something a little unnerving: There were files that were checked out and the web front end was not displaying them in any way.

More specifically I ran this query against one of my CDBs:

USE WSS_Content_IT
SELECT  
    AD.DirName, 
    AD.LeafName, 
    UI.tp_Login 
FROM 
    dbo.AllDocs AS AD FULL JOIN 
    dbo.UserInfo AS UI ON 
        UI.tp_ID = AD.CheckoutUserId 
WHERE 
    UI.tp_Login IS NOT NULL AND
    AD.DirName IS NOT NULL

...and it returned just over a thousand rows. About a dozen of them were files I expected to be checked out, but the rest all belonged in the same folder, one I remember populating weeks ago. When I used the web interface to find the offending folder it appeared to not exist. Even when I was logged in with the account listed under tp_Login the folder simply wasn't there to be browsed. These files are acting specifically like they've never had a version checked-in and the "Manage files which have no checked in version" link under Document Library Settings doesn't display these files either.

So I ran the same query against my other CDBs and discovered that this has happened to all of them. Clearly this is a problem.

So since I can't check these documents in by using the web interface, and they don't show up in SharePoint Designer either, how can I use a SQL query to force a check-in of these files?

Answer 1

YOU SHOULD NOT MODIFY A CONTENT DATABASE USING SQL

Even reading from the content databases is not considered good practice, you may cause locks which will cause SharePoint to fail.

The moment you change anything in a SharePoint database (except the usage ones) your SharePoint installation becomes unsupported.

Loop through the pages/files using PowerShell and check them in, but don't use SQL.

Read: Support for changes to the databases that are used by Office server products and by Windows SharePoint Services and yes it applies to 2010.

An example of checking in a file using Powershell could be:

$web = Get-SPWeb http://sharepoint
$file = $web.GetFile("Shared Documents/MyDoc.docx")
$file.CheckIn("Checked in using PowerShell", [Microsoft.SharePoint.SPCheckinType]::MajorCheckIn)

Answer 2

EDIT: Didn't see Per's edit in time!

I am going to post this as an answer, but you really should select Per Jackobson's as the accepted answer to the question. Here is how you can check in documents in library using PoSh. You can also used the PowerShell shell window to examine these files a little more closely. Test the code as I am posting from memory...

$web = Get-SPWeb "http://domain.com"
$list = $web.Lists["Library Name"]

foreach($item in $list.Items)
{
    if($item.File.CheckedOutType -ne "None")
    {
        $item.File.CheckIn()
        $item.File.CheckInComments = "Automated checkin."
        $item.Update()
    }
}

Answer 3

If it is only one folder/library on one site. Open the offending library, Go to library settings click manage files with no checked in version. Take ownership and check them in.