Episerver 9 – Useful SQL Commands

Like all CMS solutions, Episerver stores your website’s data within a database and provides a brilliant collection of API’s to allow developers to access that data. When we build a website, developers should never directly call the CMS database for numerous reason, scaling, upgrade-ability, performance, security to name a few. When building a website or trying to debug a live server, it can sometimes to be useful to directly query the database to find out key bits of information about the state of the platform. In today’s guide, I’m going to cover some useful SQL scripts I’ve used throughout the years.

One script I frequently use is a script that lists out all the tables in a database and all their properties:

select sysobjects.Name, syscolumns.Name
from sysobjects inner join syscolumns on sysobjects.id = syscolumns.id
where sysobjects.xtype = 'U'
order by sysobjects.Name, syscolumns.colorder

I only need to look in the database once in a blue moon and that’s usually when something’s gone wrong. I can work months at a time without needing to look into SQL so memorising the Episerver database structure seems a waste of brain cells. Instead, I usually run the above script on the CMS, or commerce database to help me quickly narrow down the tables I need to look into.

Database Size

Sometimes over a period of months/years your websites database can grow exponentially, so much so your website can start to slow down due to the size. One CMS I used to work, stored every log file entry to the database but provided no scheduled task or admin functionality to clean the log. After a period of time, this database could get to 40gb or 50gb and the site would really struggle performance wise. Luckily, Episerver is a lot better performance wise and from my experience doesn’t really suffer from this issue but sometimes it’s good to check to see how big your database has become just to keep your mind at ease. The following script will show the total number of rows in each table to help you track this down:

SELECT 
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 AS TotalSpaceKB, 
SUM(a.used_pages) * 8 AS UsedSpaceKB, 
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
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

sql_table_size

tblPageType

As the name implies, this table is used to register/store all the page types used within your website. You can use the below snippet to get a list of all the page types in your website.

select Name, Filename from tblPageType order by Name

For a more advanced view, including all the properties defined for each template you can use this script (thanks Henrik Fransas) :

select
ct.Name 'Page Type Name',
pd.Name 'Property Name'
From [dbo].[tblContentType] ct
Inner Join [dbo].[tblPropertyDefinition] pd on ct.pkID = pd.fkContentTypeID
Order by ct.Name, pd.FieldOrder

Older versions of Episerver can use:

select 
tblPageType.Name 'Page Type Name',
tblPageDefinition.Name 'Property Name'
from tblPageType inner join tblPageDefinition
on tblPageType.pkID = tblPageDefinition.fkPageTypeID
order by tblPageType.Name, tblPageDefinition.FieldOrder

sql_page_types

tblChangeLog

Every time you do a save, publish or create a new page/block you should be able to see an entry in this table. I haven’t needed to use this table since Episerver 6 but it’s good to know about it:

SELECT *
FROM [tblChangeLog]

This table can get really big. This is why Episerver ships with a ‘Change Log Auto Truncate’ scheduled task.

episerver_log_truncate

If you don’t want to enable this scheduled task, you may need to look in this table periodically to clean it.

tblScheduledItemLog

This table keeps a log of all the scheduled tasks run on your site. If you need to figure out what scheduled jobs have been run a week or a month ago, then you can use the following script:

SELECT * 
FROM [tblScheduledItemLog]

Jon D Jones

Software Architect, Programmer and Technologist Jon Jones is founder and CEO of London-based tech firm Digital Prompt. He has been working in the field for nearly a decade, specializing in new technologies and technical solution research in the web business. A passionate blogger by heart , speaker & consultant from England.. always on the hunt for the next challenge

More Posts

4 replies
  1. Henrik Fransas
    Henrik Fransas says:

    Hello

    That is for the old database, for a newer version of Epi the sql will look like this

    select
    ct.Name ‘Page Type Name’,
    pd.Name ‘Property Name’
    From [dbo].[tblContentType] ct
    Inner Join [dbo].[tblPropertyDefinition] pd on ct.pkID = pd.fkContentTypeID
    Order by ct.Name, pd.FieldOrder

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

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