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.

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.

About the author

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

View all my tips