Viewing Collation (Server/Database/Column) Via T-SQL

Hey everyone and welcome back to SQL Something!

Real quick one today and that is viewing Collation information via T-SQL (I will do a follow up post eventually on how to do it via Management Studio with pictures).

Now onto the T-SQL.

Server:

To find a server's collation setting via T-SQL we can use one of the following in a New Query window:

• SELECT CONVERT (varchar, SERVERPROPERTY('collation'));

OR

• EXECUTE sp_helpsort;

The first makes use of the 'SERVERPROPERTY' function to return server information. Can provide a wealth of server information that I may go into in a subsequent post. Result set is as follows: Latin1_General_CI_AS.

The second option uses the 'sp_helpsort' stored procedure which is strictly used to return the stored order/character set of a server. Result set is as follows: Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive.

Database:

To find a database's collation setting via T-SQL, we can use one of the following in a New Query window:

• SELECT CONVERT (varchar, DATABASEPROPERTYEX('YourDBName','collation'));

OR

• SELECT Database_Name, Database_Collation_name FROM sys.databases;

The first option makes use of the 'DATABASEPROPERTEX' function to return database information. Can provide a wealth of information. In this case it is used to display collation info for a specific database. Exchange 'YourDBName' with the actual name of your database. Result set is as follows: SQL_Latin1_General_CP1_CI_AS.

The second option lists all databases and their respective collation. Uses 'sys.databases' to retrieve this info. Can also provide a lot of other info concerning all databases. The result set is as follows:

Database_Name    Collation_Name
master                     Latin1_General_CI_AS
tempdb                    Latin1_General_CI_AS
model                      Latin1_General_CI_AS
msdb                       Latin1_General_CI_AS

Column:

To find a column's collation, open a new query window that is pointed to the database that contains the table that contains the column and use the following:

• SELECT Column_Name, Column_Collation
  FROM sys.columns
  WHERE name = 'MonthName';

PLEASE NOTE: If your database contains different tables that have identical column names, ALL columns and their collation will be displayed as follows:

Column_Name  Column_Collation
MonthName      SQL_Latin1_General_CP1_CI_AS
MonthName      SQL_Latin1_General_CP1_CI_AS
MonthName      SQL_Latin1_General_CP1_CI_AS
MonthName      SQL_Latin1_General_CP1_CI_AS
MonthName      SQL_Latin1_General_CP1_CI_AS


DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)