Pulling site collections details via direct SQL query

I certainly don't recommend making changes to your content DBs directly in SQL, but there's no harm to be had pulling data from the DB's.  I found that this was the easiest way to poll data about all farm content DBs and Web Apps listed by Site Collection.  Hope you find this useful!

/*

SiteReport.sql

written by Josef Nielsen

September 2007

 

NOTE: You must create a linked server if you use multiple SQL server to house you content DBs

*/

BEGIN

DECLARE @ts1 varchar(1000), @ConfigDB VARCHAR(128)

 

-- Set your Config DB Name here if it is different

SET @ConfigDB = 'SharePoint_Config'

 

-- This creates a temp table to hold the list of content DBs referenced by the Config DB

CREATE TABLE [#TempDbList]

      (

      DBname VARCHAR(128),

      DBInstance VARCHAR(128),

      DBServer VARCHAR(128)

      )

-- Populate the temp table with content DBs

SET @ts1 = 'INSERT INTO #TempDbList

                  SELECT [DbName].[Name] AS ''DatabaseName'',

                        [Instance].[Name] AS ''DatabaseInstance'',

                        [Server].[Name] AS ''DatabaseServer''

                  FROM '+'['+@ConfigDB+']'+'.[dbo].[Objects] AS [DbName]

                        LEFT JOIN '+'['+@ConfigDB+']'+'.[dbo].[Objects] AS [Instance]

                              ON [DbName].[ParentId] = [Instance].[ID]

                        LEFT JOIN '+'['+@ConfigDB+']'+'.[dbo].[Objects] AS [Server]

                              ON [Instance].[ParentId] = [Server].[Id]

                  WHERE [DbName].[Properties] LIKE ''%SPContentDatabase%''

                        AND [DbName].[Properties] NOT LIKE ''%WebApplication%'''

EXEC (@ts1)

 

DECLARE @ts2 VARCHAR(1000)

 

--This creates a temp table to hold the end results of the Site Collection lists from all Content DBs

CREATE TABLE [#TempSiteList]

(

    FullURL VARCHAR(128),

    WebApp VARCHAR(128),

    DBServer VARCHAR(128),

    DBName VARCHAR(128),

    Megs BIGINT,

    Quotamax VARCHAR(128),

    Quotawarning VARCHAR(128),

    Userquota VARCHAR(128),

    Siteowner VARCHAR(128),

    OwnerEmail VARCHAR(128),

    Sitecreationdate VARCHAR(128),

    Lastcontentchange VARCHAR(128)

)

 

-- Create a cursor to walk through each content DB

DECLARE DB_cursor CURSOR

      FOR

            SELECT [DBServer], [DBInstance], [DBName]

 

            FROM [#TempDbList]

OPEN DB_Cursor

DECLARE @vDBServer VARCHAR(128)

DECLARE @vDBInstance VARCHAR(128)

DECLARE @vDBName VARCHAR(128)

FETCH NEXT FROM DB_cursor INTO @vDBServer, @vDBInstance, @vDBName

WHILE @@FETCH_STATUS = 0

      BEGIN

      DECLARE @DBv1 VARCHAR(2000)

 

-- Add a backslash for DBServers that are not default instances

      DECLARE @slash VARCHAR(128)

      IF @vDBInstance = ''

            SET @slash = ''

      ELSE

            SET @slash = '\'

-- Script to insert Site Collection details to the temp site summery table

      SET @DBv1 = 'INSERT INTO [#TempSiteList]

            SELECT [Webs].[FullUrl],

            [ConfigObjects].[Name] AS ''WebApp'',

            (SELECT ''' + @vDBServer+@slash+@vDBInstance + ''') AS ''SQL Server'',

            (SELECT ''' + @vDBName + ''') AS ''Content DB Name'',

            (([Sites].[diskused])/1024)/1024 AS ''Megs'',

            (([Sites].[diskquota])/1024)/1024 AS ''Quota max'',

            (([Sites].[diskwarning])/1024)/1024 AS ''Quota warning'',

            (([Sites].[userquota])/1024)/1024 AS ''User Quota'',

            [User].[tp_login] AS ''Site Owner'',

            [User].[tp_email] AS ''Owner E-mail'',

            CAST([Sites].[timecreated] AS char(30)) AS ''Site Creation Date'',

            CAST([Sites].[lastcontentchange] AS char(30)) AS ''Last Content Change''

      FROM

            ['+@vDBServer+@slash+@vDBInstance+'].['+@vDBName+'].[dbo].[sites] AS [Sites] WITH (NOLOCK)

            LEFT JOIN ['+@vDBServer+@slash+@vDBInstance+'].['+@vDBName+'].[dbo].[webs] AS [Webs] WITH (NOLOCK) ON [Webs].[siteID] = [Sites].[Id]

            LEFT JOIN ['+@vDBServer+@slash+@vDBInstance+'].['+@vDBName+'].[dbo].[userinfo] AS [User]  WITH (NOLOCK) ON [User].[tp_SiteID] = [Sites].[Id]

            LEFT JOIN '+'['+@ConfigDB+']'+'.[dbo].[SiteMap] AS [ConfigSiteMap]  WITH (NOLOCK) ON [ConfigSiteMap].[Id] = [Sites].[Id]

            LEFT JOIN '+'['+@ConfigDB+']'+'.[dbo].[Objects] AS [ConfigObjects]  WITH (NOLOCK) ON [ConfigSiteMap].[ApplicationID] = [ConfigObjects].[Id]

      WHERE

            [Webs].[ParentWebId] IS NULL

            --AND [User].[tp_SiteID] = [Sites].[Id]

            AND [User].[tp_SiteAdmin] = 1

            AND [User].[tp_id] = 1'

      EXEC (@DBv1)

 

FETCH NEXT FROM DB_cursor INTO @vDBServer, @vDBInstance, @vDBName

END

 

CLOSE DB_cursor

DEALLOCATE DB_Cursor

END

-- Cursor is closed and released, ad now we select the results of the scan

SELECT * FROM #TempSiteList ORDER BY [WebApp], [FullURL]

 

-- Clean up to get rid of those temp tables

DROP TABLE [#TempDbList]

DROP TABLE [#TempSiteList]