Pulling site collections details via direct SQL query by Josef Nielsen

This article is a direct grab from the site http://www.sharepointblogs.com/echef/archive/2007/10/09/pulling-site-collections-via-direct-sql-query.aspx



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 ‘+‘[‘[email protected]+‘]’+‘.[dbo].[Objects] AS [DbName]

                        LEFT JOIN ‘+‘[‘[email protected]+‘]’+‘.[dbo].[Objects] AS [Instance]

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

                        LEFT JOIN ‘+‘[‘[email protected]+‘]’+‘.[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 ”’ + @[email protected][email protected] + ”’) 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 [‘[email protected][email protected][email protected]+‘].[‘[email protected]+‘].[dbo].[userinfo] AS [User]  WITH (NOLOCK) ON [User].[tp_SiteID] = [Sites].[Id]

            LEFT JOIN ‘+‘[‘[email protected]+‘]’+‘.[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] GO 

 

— Clean up to get rid of those temp tables

DROP TABLE [#TempDbList]

DROP TABLE [#TempSiteList] 

Leave a Reply

Your email address will not be published. Required fields are marked *