In this tutorial, you will gain access to some handy SQL snippets for Umbraco CMS. Sometimes for maintenance, or, troubleshooting reasons, it can be useful to query the Umbraco database directly to get extra information. For example, checking if a content editors account is locked can be handy. If you want to access the most useful Umbraco SQL scripts that I've used over the years, this is the tutorial for you 🔥🔥🔥
WARNING: Before going too far into this tutorial, I should give a warning. Reading data from Umbraco is fine. Writing data is not. The Umbraco team build the APIs assuming the database shape. The upgrade wizard works based on known schemas. My advice is to never modify or change the data or the schema of your Umbraco database. Writing custom SQL to change the Umbraco database will likely break Umbraco. Be warned ❌
What Do The Database Tables Do?: One script I use very frequently is a script that lists out all the tables within a database and all the table properties. This script can help me figure out which table I need to query next:
Check Database Table Size: Over a period of months and years, your website's database can grow exponentially, so much so your website can start to slow down due to the size. The following script will list the total number of rows in each table in the Umbraco database and the size each table takes up: If you have site performance issues, this is the first script to run:
Clear The Logs: Umbraco contains an audit log of errors, warnings and notifications actions performed within the backend. This table, umbracoLog, can sometimes get too large with historical data which can impact site performance. Uless you need to debug a specific issue, you will never need to use the data within umbracoLog after 48 hours. The fix is to clear this table. This is the only database write command that is safe within Umbraco, also back-up your database first 😉
Moving Multiple Documents To A Different Parent: If you've ever worked on a site redesign project you will likely have needed to move lots of content. Moving lots of pages just using the Umbraco Move feature in the editor can sometimes be cumbersome. If you need to move a lot of pages within the CMS, a SQL script can be quicker. Granted this is a red flag script, so use it at your own risk. As usual, backup your database up first!
Find Content Within A Page: If you want to find all instances of some content within your site, you can use this script to query all the related pages. Maybe you need to find all references to some code, a link, or a typo. Searcing all pages using SQL is often much quicker. I used this script to rename all references to Episerver within my content, to EPIserver:
To use the script above, you need to update the script to use the keyword and the property that you want to query 💥
Happy Coding 🤘
