EPiServer CMS 11 Useful SQL Queries - 1

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

Check EPiServer DB Version

DECLARE @db_status int;  
EXEC @db_status = dbo.sp_DatabaseVersion;  
SELECT 'DB Status' = @db_status;  

GO