Here is a set of few queries that we have been using in different investigations
- Check How Big is your Database
- Get Data for Each Property and from Each Content Type
- Complete Tree Structure of your web site
- Check EPiServer DB Version
Check How Big is Database
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB,
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS TotalSpaceMB,
SUM(a.used_pages) * 8 AS UsedSpaceKB,
CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB,
(SUM(a.total_pages) – SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
CAST(ROUND(((SUM(a.total_pages) – SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
WHERE
t.NAME NOT LIKE ‘dt%’
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
Get Data for Each Property and from Each Content Type
SELECT tblContentType.Name AS TypeName, tblContentLanguage.Name AS ContentName, tblContentLanguage.URLSegment, tblPropertyDefinition.Name As PropertyName, CASE WHEN tblContentProperty.Number IS NULL AND tblContentProperty.FloatNumber IS NULL AND
tblContentProperty.ContentType IS NULL AND tblContentProperty.ContentLink IS NULL AND tblContentProperty.Date IS NULL AND tblContentProperty.String IS NULL AND tblContentProperty.LongString IS NULL
THEN ‘Boolean: ‘ + CAST(tblContentProperty.Boolean AS varchar(40)) WHEN tblContentProperty.Number IS NOT NULL THEN ‘Number: ‘ + CAST(tblContentProperty.Number AS varchar(40))
WHEN tblContentProperty.FloatNumber IS NOT NULL THEN ‘FloatNumber: ‘ + CAST(tblContentProperty.FloatNumber AS varchar(40)) WHEN tblContentProperty.ContentType IS NOT NULL
THEN ‘PageType: ‘ + CAST(tblContentProperty.ContentType AS varchar(40)) WHEN tblContentProperty.ContentLink IS NOT NULL THEN ‘PageLink: ‘ + CAST(tblContentProperty.ContentLink AS varchar(40))
WHEN tblContentProperty.Date IS NOT NULL THEN ‘Date: ‘ + CAST(tblContentProperty.Date AS varchar(40)) WHEN tblContentProperty.ContentType IS NOT NULL THEN ‘String: ‘ + CAST(tblContentProperty.String AS varchar(40))
WHEN tblContentProperty.LongString IS NOT NULL THEN ‘LongString: ‘ + CAST(tblContentProperty.LongString AS varchar(40)) ELSE CAST(‘Error Determining Value!’ AS varchar(40)) END AS ‘Property Value’
FROM tblContent INNER JOIN
tblContentLanguage ON tblContent.pkID = tblContentLanguage.fkContentID INNER JOIN
tblContentProperty ON tblContent.pkID = tblContentProperty.fkContentID INNER JOIN
tblPropertyDefinition ON tblContentProperty.fkPropertyDefinitionID = tblPropertyDefinition.pkID LEFT OUTER JOIN
tblContentType ON tblPropertyDefinition.fkContentTypeID = tblContentType.pkID AND tblContent.fkContentTypeID = tblContentType.pkID
Complete Tree Structure of your web site
IF EXISTS (SELECT name FROM sysobjects
WHERE name = ‘ShowHierarchy’ AND type = ‘P’)
DROP PROCEDURE ShowHierarchy
go
CREATE PROC dbo.ShowHierarchy ( @Root int ) AS BEGIN
SET NOCOUNT ON
DECLARE @PageID int, @PageName varchar(30)
SET @PageName = (SELECT tblContentLanguage.Name FROM dbo.tblContent inner join tblContentLanguage on tblContent.pkID = tblContentLanguage.fkContentID WHERE pkID = @Root)
PRINT REPLICATE( ‘-‘, @@NESTLEVEL * 4) + @PageName
SET @PageID = (SELECT MIN( pkID ) FROM dbo.tblContent WHERE fkParentID = @Root)
WHILE @PageID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @PageID
SET @PageID = (SELECT MIN( pkID ) FROM dbo.tblContent
WHERE fkParentID = @Root AND pkID > @PageID)
END
END
go
ShowHierarchy 103–[Replace with HOME PAGE ID]
go
Output
—-Home
——–PagePlaceHolder1
————page 1
————page 2
—————-page 21
—————-page 22
——————–page 221
————————page 2211
————————page 2212
————————page 2213
——————–page 222
——————–page 223
—————-page 23
DECLARE @db_status int;
EXEC @db_status = dbo.sp_DatabaseVersion;
SELECT ‘DB Status’ = @db_status;
GO