Handy SQL for translations

Here are a few handy SQL snippets for dealing with translated content on a Drupal site. These will be helpful if you are trying to translate a site and need to work out what content hasn't been translated yet.

1. Find nodes that haven't been translated.

SELECT 
  nid, title, language 
FROM node 
WHERE nid=tnid AND nid NOT IN (SELECT tnid FROM node WHERE language="es")

2. Find blocks that haven't been translated.

SELECT *
FROM locales_source
WHERE source IN (SELECT body FROM boxes) 
  AND lid NOT IN (SELECT lid FROM locales_target) 

3. Find blocks that are not in the locales_source table. These will be blocks that haven't been viewed in a translated language.

SELECT * 
FROM boxes 
WHERE body NOT IN (SELECT source FROM locales_source)