View Collation Information

  • Article

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.

[Top]

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.

    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 SQL Server 2012

  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.

    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.

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

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

[Top]

See Also

Reference

SERVERPROPERTY (Transact-SQL)

sys.fn_helpcollations (Transact-SQL)

sys.databases (Transact-SQL)

sys.columns (Transact-SQL)

Collation Precedence (Transact-SQL)

sp_helpsort (Transact-SQL)