Umbraco 7 – Useful SQL Commands

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

Database Size

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

sql_table_size

umbracoLog

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 [email protected] where [email protected]
-- Next update all the path columns for the children.
SELECT id,[path] into #temp FROM [umbracoNode] where [email protected]
while exists(select Id from #temp)
begin
select @Id=Id,@Path=[Path] from #temp
select @Path = REPLACE(@Path,@OldParentId,@ParentId)
update umbracoNode set [email protected] where [email protected]
print cast(@Id as varchar)+': ' [email protected]
delete from #temp where [email protected]
end

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

0 replies

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 *