view-collation-information.md

title	View Collation Information | Microsoft Docs
ms.custom
ms.date	03/06/2017
ms.prod	sql-server-2014
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
manager	craigg

View Collation Information

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, 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, 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>';  
   

See Also

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