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 on 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:
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.
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:
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.