I was recently perusing the the MSDN SQL Server forums and noticed a question about localization of Microsoft SQL Server messages. I replied to the post with a mention of the sys.messages table - the table where SQL Server messages are stored. I blogged about this system table a few months ago.

Afterward I started playing around some more with the sys.messages table. I started with the following basic query.

The first thing to notice is that each message_id has one or more languages associated with it. Message_id 21, for example, has a row for language_id 1033, 1031, 1036, etc. It's fairly obvious to me that language_id 1033 is my native tongue, English. And I can make some educated guesses on some of the other languages. But where are they defined?

Well, the syslanguages system table contains a list of each language that SQL Server recognizes. For example, run the following query in a Management Studio Query Window.

1033 is indeed US English. And 1031 is German, 1036 is French, and so on. There are 33 languages in all - US English and British English are considered two different languages in here, though they share the same messages.

But what's interesting is that not all languages in the syslanguages system table are represented in the sys.messages table. For example, consider the following query.

SELECT 
     m.language_id
    ,l.alias     
    ,COUNT(*)
FROM 
    sys.messages AS m JOIN
    syslanguages AS l 
        ON l.msglangid = m.language_id
GROUP BY 
     l.alias     
    ,m.language_id 

The results indicate that only 11 languages (10 if you consider US English and British English to be one and the same) are represented in the sys.messages table. 

So, that must mean that the other languages don't have a need for messages. :) Right, if that's that case, remove US English from the list!

Actually if a language is not present in the sys.messages table, a default language will be used instead. For more information, check out the sp_addmessage procedure and the sys.messasges system table in Books Online.

Cheers!

Joe