Umbraco provides us with a rich API for dealing with Umbraco content and data. Sometimes for maintenance reasons, or troubleshooting in development it can be useful to query the Umbraco database directly to help figure things out. In today's guide, I'm going to cover some useful SQL scripts I've used throughout the years when I've worked with Umbraco.
Should I Query The Umbraco Database Directly?
Like all CMS solutions the Umbraco website's data is stored in a database. As web developers, we do not own this database. Umbraco has the ability to change database tables and rows during upgrades, as they see fit. For this reason, you should always use the API's to access your Umbraco data. You should never try to write your own inline SQL, or create a cheeky stored procedure because, when you upgrade, your life will be a misery. The scripts listed in this page are about helping you troubleshoot issues and improve performance. This is in no way a recommendation for you to talk directly to the Umbraco database in your code!
What Do The Database Tables Do?
One script I frequently use, is a script that lists out all the tables in a database and all their properties. Like most people I only venture into the Umbraco database once in blue moon. In most instances, I can use this script to give me a brief reminder for the area I'm looking for:
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
Sometimes over a period of months/years your website's database can grow exponentially, so much so your website can start to slow down due to the size. Umbraco stores a lot of error and debugging information in the 'umbracoLog' table. If this table gets too large your website's performance can be affected. The following script will list the total number of rows in each table in the Umbraco database and the size each table takes up.
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
If you don't regularly maintain your Umbraco database, the umbracoLog can become quite large at times. The umbracoLog table is the dumping ground for all Umbraco errors, warnings, and notifications. Unless you need to debug a specific issue, most of this data isn't needed after a few weeks. If you don't clear this up regularly, the table can grow very large quickly. When the log table gets too big, it can affect the performance of your page load times so regularly maintaining and truncating this table is a must. If your umbracoLog is too big, you can run the below script to clean it up:
TRUNCATE TABLE [dbo].umbracoLog
Moving Multiple Documents To A Different Parent
If you've ever worked on a re-design project, a lot of the times we need to move data between sections and parents as the website changes. If you need to move a lot of documents at once to a new parent, it’s painful doing it inside the editor. With any manual SQL scripts, backup your database first.
set nocount on declare @OldParentId int declare @ParentId int declare @Id int declare @Path nvarchar(150) select @OldParentId = 1139 -- Set to the existing parent node select @ParentId = 2457 -- Set to the new parent node -- First update the parentid column update umbracoNode set parentid=@ParentId where parentId=@OldParentId -- Next update all the path columns for the children. SELECT id,[path] into #temp FROM [umbracoNode] where parentid=@ParentId while exists(select Id from #temp) begin select @Id=Id,@Path=[Path] from #temp select @Path = REPLACE(@Path,@OldParentId,@ParentId) update umbracoNode set path=@Path where id=@Id print cast(@Id as varchar)+': ' +@Path delete from #temp where Id=@Id end
Find Content Within A Page
SELECT cmd.text, propData.propertytypeid, propData.dataNtext FROM ( SELECT cmsContent.ContentId, cmsContent.VersionId, cmsContent.VersionDate , ROW_NUMBER() OVER (PARTITION BY cv.ContentId ORDER BY cv.VersionDate DESC) AS rn FROM cmsContentVersion cmsContent ) v LEFT JOIN umbracoNode umbNode ON umbNode.id = v.ContentId LEFT JOIN cmsPropertyData propData ON umbNode.id = propData.contentNodeId AND v.VersionId = propData.VersionId LEFT JOIN cmsPropertyType propType ON propData.propertytypeid = propType.id LEFT JOIN [dbo].[cmsDocument] cmd ON cmd.nodeId = umbNode.id WHERE v.rn = 1 and propType.[Name] = 'Main Content' and propData.dataNtext LIKE '%<<TEXT GOES HERE>>%'