Episerver 9 - Useful SQL Commands
Tue 5 July, 2016 / By Jon D Jones
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.
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:
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
sys.indexes i ON t.OBJECT_ID = i.object_id
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
t.NAME NOT LIKE 'dt%'
AND t.is_ms_shipped = 0
AND i.OBJECT_ID > 255
t.Name, s.Name, p.Rows
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) :
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:
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
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:
This table can get really big. This is why Episerver ships with a 'Change Log Auto Truncate' scheduled task.
If you don't want to enable this scheduled task, you may need to look in this table periodically to clean it.
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: