T-SQL pocket knife code

Posted by Robin Hermanussen on June 21, 2012 · 1 min read

Sometimes I like to dive into the SQL databases that hold all of our precious Sitecore content and go wild. No really; sometimes the Sitecore tree, XPath builder or search function are just not the easiest tools to research a particular problem. Once you understand the database schema of Sitecore (which is not that difficult), querying the databases directly can be of great help.

Here is a script that creates 2 handy functions that you can use to make querying Sitecore data just a little easier:

After running this script (be sure to change the statement in line 1 to match your database name), you can write queries such as this one:

This results in:

Screenshot of the result of the previous query

Since the SC_GetPath function uses recursion, make sure your query does not go out of control. SQL Server does not allow too many recursions by default.