/*
ContentDBReport.sql
written by Josef Nielsen
October 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),
MaxSites INT,
WarnSites INT
)

-- 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'',
CONVERT(XML, [DbName].[properties]).value (''(/object/sFld/text())[1]'', ''int'') AS ''MaxSites'',
CONVERT(XML, [DbName].[properties]).value (''(/object/sFld/text())[2]'', ''int'') AS ''WarnSites''
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 [#TempContentDbList]
(
WebApp VARCHAR(128),
DBServer VARCHAR(128),
DBName VARCHAR(128),
DBSites int,
DBWarnSites int,
DBMaxSites int,
DBSize float
)

-- Create a cursor to walk through each content DB
DECLARE DB_cursor CURSOR
FOR
SELECT [DBServer], [DBInstance], [DBName], [MaxSites], [WarnSites]
FROM [#TempDbList] 

OPEN DB_Cursor

-- Declare Variables to populate by Cursor
DECLARE @vDBServer VARCHAR(128)
DECLARE @vDBInstance VARCHAR(128)
DECLARE @vDBName VARCHAR(128)
DECLARE @vMaxSites INT
DECLARE @vWarnSites INT
DECLARE @DBv1 VARCHAR(5000) 

FETCH NEXT FROM DB_cursor INTO @vDBServer, @vDBInstance, @vDBName, @vMaxSites, @vWarnSites
WHILE @@FETCH_STATUS = 0
BEGIN  

-- Add a backslash for DBServers that are not default instances
DECLARE @slash VARCHAR(5)
IF @vDBInstance = ''
SET @slash = ''
ELSE
SET @slash = '\'

-- Script to insert Content DB details to the temp site summery table
SET @DBv1 = 'INSERT INTO [#TempContentDbList]
SELECT [ConfigObjects].[Name] AS ''WebApp'',
(SELECT ''' + @vDBServer+@slash+@vDBInstance + ''') AS ''SQL Server'',
(SELECT ''' + @vDBName + ''') AS ''Content DB Name'',
(SELECT COUNT([Webs].[Title])) AS ''Current Site Count'',
(SELECT ' + CONVERT(VARCHAR(20), @vMaxSites) + ') AS ''Max Site Count'',
(SELECT ' + CONVERT(VARCHAR(50),@vWarnSites) + ') AS ''Site Size'',
(SELECT round(sum(convert(float,[size])*8/1024),2) AS ''DB Size in MB''
FROM
[sys].[master_files]
WHERE
[state] = 0
AND [data_space_id] = 1
AND db_name([database_id]) = ''' + @vDBName + '''
GROUP BY [database_id]) AS ''Site Size''
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 '+'['+@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
GROUP BY [ConfigObjects].[Name]' 

EXEC (@DBv1)

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

END

CLOSE DB_cursor
DEALLOCATE DB_Cursor

END

-- Cursor is closed and released, and now we select the results of the scan
SELECT * FROM #TempContentDbList ORDER BY [WebApp], [DBName]

GO

-- Clean up to get rid of those temp tables
DROP TABLE [#TempDbList]
DROP TABLE [#TempContentDbList]