Enumerating all Document Libraries and Form Libraries in a Site Collection Using SQL

Recently I had a situation where I needed to produce a list of all Document Libraries and Forms Libraries within a site collection along with associated template attached to them. The site collection was quite huge with the database size of over 75 Gigs and thousands of libraries and the environment was SharePoint Portal Server 2003

I did not have any tools like Visual Studio where I could write the code and run it against the site collection. I found the following code which logically should have worked

using(SPSite site = new SPSite("http://myserverurl"))
{
     using(SPWeb rootWeb = site.OpenWeb())
     {
     ActionDocLibRecursive(rootWeb);
     }
}

The recursive method would be as follows:

private void ActionDocLibRecursive(SPWeb web)
{
    // loop through each list in the web site
    foreach(SPList list in web.Lists)
    {
    // check if the list is a document library
        if(list.BaseType == SPBaseType.DocumentLibrary)
        {
        // if it is, create a document library object
        SPDocumentLibrary docLib = (SPDocumentLibrary)list;
        // check that the document library is not a "catalog"
        // (e.g. "Master Pages & Page Layouts" .. or "Web Template Gallery")
        if(docLib.IsCatalog == false)
        {
                // perform action .. you've found a document library
                // and it's not a "Catalog" !
        }
    }
}

// call the recursive loop back on itself

    foreach(SPWeb subWeb in web.Webs)
    {
        // call resursive method on each sub-site of the current site
        ActionDocLibRecursive(subWeb);
    }
}

To get reference all various list types and its internal List Type ID go here http://msdn.microsoft.com/en-us/library/ms462947.aspx

But now what would be another way of doing the same task… I opened up the SQL Management Studio and then constructed the following query

SELECT
"Template Type" = CASE
WHEN [Lists].[tp_ServerTemplate] = 101 THEN 'Doc Lib'
WHEN [Lists].[tp_ServerTemplate] = 115 THEN 'Form Lib'
ELSE 'Unknown'
END,
"List URL" = 'http://portal.adatum.com/' + CASE
WHEN [Webs].[FullUrl]=''
THEN [Webs].[FullUrl] + [Lists].[tp_Title]
ELSE [Webs].[FullUrl] + '/' + [Lists].[tp_Title]
END,
"Template URL" = 'http://portal.adatum.com/' +
[Docs].[DirName] + '/' + [Docs].[LeafName]
FROM [Lists] LEFT OUTER JOIN [Docs] ON [Lists].[tp_Template]=[Docs].[Id], [Webs]
WHERE ([Lists].[tp_ServerTemplate] = 101 OR [Lists].[tp_ServerTemplate] = 115)
   AND [Lists].[tp_WebId]=[Webs].[Id]
order by "List URL"

Getting Results in Excel

– Open a blank sheet go to “Data” tab and then “From Other Sources” select “From SQL Server”
– In the wizard give the server name and use applicable authentication (Windows or SQL)
– Select the database which is used by the site collection for which you want the extract the information and then click on “Next”
– Click on finish
– In “Import Data” dialog box click on “Properties…”
– Switch to the “Definition” tab and select the “Command Type” as “SQL”
– In “Command Text” paste the query above
– Now back to “Import Data” dialog box click on ok

The above method is dirty way of doing the job and it will require direct SQL Access fortunately which I did have. But for people who does not have the access to database then only way would be write the custom code which I stated at top of the post.

If you want to enumerate the same set of data across multiple site collections then following piece of code can be used with calling the earlier code in recursion

First we need to create a new System.Url object and pass that to the static method Lookup on the SPWebApplication class.

Uri serverUri = new
Uri(”http://localhost”);

SPWebApplication webApplication = SPWebApplication.Lookup(serverUri);

//Now we have an instance of SPWebApplication
//we can enumerate it’s Sites collection like so:

foreach (SPSite siteCollection in webApplication.Sites)
{
	//You can use the code at start of this post to recurse the site collection
}