view-collation-information.md

title	description	author	ms.author	ms.date	ms.service	ms.topic	helpviewer_keywords	monikerRange
View Collation Information	View Collation Information	WilliamDAssafMSFT	wiassaf	03/14/2017	sql	how-to	collations [SQL Server], view	=azuresqldb-current||>=sql-server-2016||>=sql-server-linux-2017||=azuresqldb-mi-current

View Collation Information

[!INCLUDE SQL Server Azure SQL Database Azure SQL Managed Instance]

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 [!INCLUDEssnoversion]

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)