view-collation-information.md

title	View Collation Information | Microsoft Docs
ms.custom
ms.date	03/14/2017
ms.prod	sql
ms.reviewer
ms.technology
ms.topic	conceptual
helpviewer_keywords	collations [SQL Server], view
ms.assetid	1338b4ea-7142-44bc-a3b9-44e54431405f
author	stevestein
ms.author	sstein
monikerRange	=azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

View Collation Information

[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md]

You can view the collation of a server, database, or column in [!INCLUDEssManStudioFull] using Object Explorer menu options or by using [!INCLUDEtsql].

How to View a Collation Setting

You can use one of the following:

• SQL Server Management Studio

• Transact-SQL

Using SQL Server Management Studio

To view a collation setting for a server (instance of SQL Server) in Object Explorer

1. In Object Explorer, connect to an instance of [!INCLUDEssDE].

2. Right-click the instance and select Properties.

To view a collation setting for a database in Object Explorer

1. In Object Explorer, connect to an instance of [!INCLUDEssDE] and then expand that instance.

2. Expand Databases, right-click the database and select Properties.

To view a collation setting for a column in Object Explorer

1. In Object Explorer, connect to an instance of [!INCLUDEssDE] and then expand that instance.

2. Expand Databases, expand the database and then expand Tables.

3. Expand the table that contains the column and then expand Columns.

4. Right-click the column and select Properties. If the collation property is empty, the column is not a character data type.

Using Transact-SQL

To view the collation setting of a server

1. In Object Explorer, connect to an instance of the [!INCLUDEssDE] and on the toolbar, click New Query.

2. In the query window, enter the following statement that uses the SERVERPROPERTY system function.

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

3. Alternatively, you can use the sp_helpsort system stored procedure.

   EXECUTE sp_helpsort;  

To view all collations supported by [!INCLUDEssCurrent]

1. In Object Explorer, connect to an instance of the [!INCLUDEssDE] and on the toolbar, click New Query.

2. In the query window, enter the following statement that uses the SERVERPROPERTY system function.

   SELECT name, description FROM sys.fn_helpcollations();  

To view the collation setting of a database

1. In Object Explorer, connect to an instance of the [!INCLUDEssDE] and on the toolbar, click New Query.

2. In the query window, enter the following statement that uses the sys.databases system catalog view.

   SELECT name, collation_name FROM sys.databases;  

3. Alternatively, you can use the DATABASEPROPERTYEX system function.

   SELECT CONVERT (varchar(256), DATABASEPROPERTYEX('database_name','collation'));  

To view the collation setting of a column

1. In Object Explorer, connect to an instance of the [!INCLUDEssDE] and on the toolbar, click New Query.

2. In the query window, enter the following statement that uses the sys.columns system catalog view.

   SELECT name, collation_name FROM sys.columns WHERE name = N'<insert character data type column name>';  

To view the collation settings for tables and columns

1. In Object Explorer, connect to an instance of the [!INCLUDEssDE] and on the toolbar, click New Query.

2. In the query window, enter the following statement that uses the sys.columns system catalog view.

   SELECT t.name TableName, c.name ColumnName, collation_name  
   FROM sys.columns c  
   inner join sys.tables t on c.object_id = t.object_id;  

See Also

SERVERPROPERTY (Transact-SQL)
sys.fn_helpcollations (Transact-SQL)
sys.databases (Transact-SQL)
sys.columns (Transact-SQL)
Collation Precedence (Transact-SQL)
Collation and Unicode Support
sp_helpsort (Transact-SQL)