View Collation Information

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

You can view the collation of a server, database, or column in SQL Server Management Studio using Object Explorer menu options or by using Transact-SQL.

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 Database Engine.

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 Database Engine 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 Database Engine 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 Database Engine and on the toolbar, click New Query.

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

   SQL

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

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

   SQL

   EXECUTE sp_helpsort;  
   

To view all collations supported by SQL Server

1. In Object Explorer, connect to an instance of the Database Engine and on the toolbar, click New Query.

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

   SQL

   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 Database Engine and on the toolbar, click New Query.

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

   SQL

   SELECT name, collation_name FROM sys.databases;  
   

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

   SQL

   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 Database Engine and on the toolbar, click New Query.

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

   SQL

   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 Database Engine and on the toolbar, click New Query.

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

   SQL

   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)