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.
