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] GO
-- Clean up to get rid of those temp tables
DROP TABLE [#TempDbList]
DROP TABLE [#TempSiteList]
Posted on SharePoint Blogs
Del.icio.us |
Digg It |
Technorati |
Blinklist |
Furl |
reddit |
DotNetKicks
Read the complete post at http://www.sharepointblogs.com/echef/archive/2007/10/09/pulling-site-collections-via-direct-sql-query.aspx