Identify SQL Server Instance and Database Collation Using T-SQL and SSMS

By: Ashish Kumar Mehta | Updated: 2011-10-18 | Comments (6) | Related: 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | More > Database Administration

Problem

How can I find out the collation used by the SQL Server instance and database on my SQL Servers? In this tip we look at different ways by which one can identify the collation used by a database and a SQL Server instance.

Solution

In this tip we will take a look at different ways by which you can easily identify the collation used by a database and at a SQL Server instance level. This tip will cover the following topics:

1. Identify SQL Server database collation using T-SQL
2. Identify SQL Server database collation using SQL Server Management Studio
3. Identify SQL Server instance collation using T-SQL
4. Identify SQL Server instance collation using SQL Server Management Studio

Let's take a look at each of the above options in detail.

Identify SQL Server Database Collation Using T-SQL

A Database Administrator (DBA) can execute the below T-SQL script to quickly identify the collation used by the AdventureWorks database.

/* Identify Collation for a SQL Server database */
DECLARE @DatabaseName as SYSNAME
SET @DatabaseName = 'AdventureWorks'  /* Replace Database Name Here....*/
SELECT 
 DB_NAME(DB_ID(@DatabaseName)) AS DatabaseName
,DATABASEPROPERTYEX(@DatabaseName, 'Collation') AS CollationUsedBySQLServerDatabase
GO

The T-SQL script below shows the collation for all of the databases on the current SQL Server instance.

/* Collation used by all the databases on a SQL Server instance */
USE Master
GO
SELECT
 NAME, 
 COLLATION_NAME
FROM sys.Databases
 ORDER BY DATABASE_ID ASC
GO

The T-SQL script below lists all the collations supported by SQL Server 2005 and above versions.

/* Returns a list of all the collations supported by SQL Server 2005 and above */
SELECT * FROM fn_helpcollations() 
GO

The T-SQL script below will provide all of the details about a particular collation.

SELECT * FROM fn_helpcollations() 
WHERE NAME = 'SQL_Latin1_General_CP1_CI_AS'
GO

Identify SQL Server Database Collation Using SQL Server Management Studio

1. Connect to SQL Server Database Instance using SQL Server Management Studio

2. In Object Explorer navigate to root | Databases | AdventureWorks, right click on the AdventureWorks databases and then select the Properties option from the drop down list as shown in the snippet below.

3. In the Database Properties window, in the left pane the General Page will be selected by default. You will be able to see the Collation used by the database under the Maintenance section as highlighted in the below snippet.

Identify SQL Server Instance Collation Using T-SQL

The T-SQL script below will identify the Collation used by the current SQL Server instance.

/* Identify SQL Server Collation Settings*/
USE Master
GO
SELECT SERVERPROPERTY('collation') AS SQLServerCollation
GO

identify sql server instance collation using t-sql

Identify SQL Server Instance Collation Using SQL Server Management Studio

1. Connect to a SQL Server Database Instance using SQL Server Management Studio

2. In Object Explorer, right click SQL Server Instance and then select the Properties option from the drop down list as shown in the snippet below.

3. In the Server Properties window, in the left pane the General Page will be selected by default. You will be able to see the Server Collation as highlighted in the below snippet.

Next Steps

Use the scripts in this tip to verify the SQL Server collations are the same across your development, test and production instances to make sure no inconsistencies exist.
If you have a situation where you need to support multiple collations, check out this tip - Handling cross database joins that have different SQL Server collations.
Check out these additional resources Using SQL Server Collations and Selecting a SQL Server Collation.

Last Updated: 2011-10-18

About the author

Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips

Related Resources

Comments For This Article

Tuesday, October 18, 2011 - 11:21:24 AM - Anna

The default installation of a new SQL server instance in U.S is SQL_Latin1_General_CP1_CI_AS.

But I read from some articles it recommend to use Latin1_General_CI_AS as common installation.

This is easily caused a mixed installlation in an enviroment. My questions is why recommended server collation is Latin1_General_CI_AS, but microsoft default in U.S is SQL_Latin1_General_CP1_CI_AS?

Thanks

Tuesday, October 18, 2011 - 4:33:36 AM - Mike	Back To Top (14863)
Thanks Ashish/Jugal. I got the collation as mentioned.

Tuesday, October 18, 2011 - 3:10:23 AM - Jugal Shah

You have to choose sort order 40. From the list box you have to select Binary order, for use with the 850 (Multilingual) character set.

Regards, Jugal Shah

Tuesday, October 18, 2011 - 3:02:05 AM - Mike

I am unable to find SQL_Latin1_General_Cp850_BIN collation in the drop down or in the list box.

Thanks,

Mike

Tuesday, October 18, 2011 - 2:08:48 AM - Ashish Kumar Mehta

Hi Mike,

While Installing SQL Server 2005 you need to choose SQL_Latin1_General_Cp850_BIN as collation rather than the default collation suggested by the SQL Server 2005 Installation wizard.

You can choose the "SQL_Latin1_General_Cp850_BIN" collation settings under the "Collation Settings” screens "collation designator ans sort order" drop down.
As per the below link SQL_Latin1_General_Cp850_BIN collation is supported in sQL Server 2005.

link: http://msdn.microsoft.com/en-us/library/ms144250(v=SQL.90).aspx

Hope this helps!

Thanks
Ashish

Tuesday, October 18, 2011 - 1:32:16 AM - Mike

Hello Ashish,

Can you please let me know how can I select SQL_Latin1_General_CP850_BIN colation while SQL Server 2005 installation?

if possible can you please post your Bio and picture.

Thanks,

Mike