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 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 Update: 2011-10-18
About the author

View all my tips
- How to change server level collation for a SQL Ser...
- Changing SQL Server Collation After Installation...
- Script and Alter SQL Server Database Objects with ...
- How column COLLATION can affect SQL Server query p...
- Create SQL Server temporary tables with the correc...
- Identify SQL Server Instance and Database Collatio...
- Handling cross database joins that have different ...
- How to Change the COLLATION of a SQL Server Column...
- Case Sensitive Search on a Case Insensitive SQL Se...
- More SQL Server DBA Tips...