An SQL script to get all dictionary items for all languages in a single table

An SQL script to get all dictionary items for all languages in a single table

I was developing a multi-language Umbraco site for a client the other day. That specific site featured five languages. 

The client was very familiar with the Umbraco Dictionary, but they wanted a quick way to look at all the translations at once - ideally, laid out in the form of a spreadsheet or something similar that would allow for a quick review of all dictionary terms for all languages.

So, to cut a long story short, I made this. (Don't worry, the client knows I'm blogging this :) ) 

This is a relatively simple SQL script that you can use on its own (or convert it into a stored procedure) and what it does is produce a pivoted table containing all dictionary keys and all the different values for each key, each value under its respective language column. 

I suspect you frowned when you saw the dynamic SQL execution inside the script but I hope you understand that this is the only way to have a dynamic pivot since we don't know how which or how many languages will be there.

When you run the script you get something like this (please excuse the nulls and the empty fields, it's from a development Umbraco install which hasn't been fully localized yet. Also, if the contents of the "el" column is all Greek to you, it's because they actually are Greek. :) 

You can get the output of this query, paste it into an Excel spreadsheet and off you go. Just thought it would be useful to share.

EDIT: At the time of writing this article I wasn't aware of Kevin Jump's excellent Translation Manager package which can do a lot more things (it's commercial so you have to pay for it, but I suspect it's worth its money). Have a look:

Until next time!