How to: quickly check SQL server collation settings

Just a quick note on how to quickly check Microsoft SQL server collation without wading through SQL Server Management Studio GUI. Go to CMD and execute the following:

\n\n\n[code language=”sql” light=”true”]\nsqlcmd -q "SELECT CONVERT (varchar, SERVERPROPERTY(‘collation’));"\n[/code]\n\n

Alternatively you may use sp_helpsort stored procedure, though when run via sqlcmd it gives a bit strange output if run with sqlcmd

\n\n\n[code language=”sql” light=”true”]\nsqlcmd -q "EXECUTE sp_helpsort;"\n[/code]\n\n

On the boxes with SQL server installed path to sqlcmd executable added to PATH variable so you can fire it off without specifying full path. I guess you may also check corresponding key in registry which should exist there I believe (at least for server collation) 🙂

Also you may view collation settings on databases:

\n\n\n[code language=”sql” light=”true”]\nsqlcmd -q "SELECT name, collation_name FROM sys.databases;"\n[/code]\n\n

Reference: MSDN: View Collation Information

Leave a Reply

Your email address will not be published. Required fields are marked *