Post
Ask Question

SQL Server - Get The Collation Details

In this blog, we will learn how to get the below collation details using "SERVERPROPERTY".
  • Collation.
  • CollationID.
  • ComparisonStyle.
  • LCID.
  • SqlCharSet.
  • SqlCharSetName.
  • SqlSortOrder.
  • SqlSortOrderName.
What's the "SERVERPROPERTY"?

"SERVERPROPERTY" is a System-defined function used to return the SQL Server Instance Information.

"SERVERPROPERTY" Syntax

  1. SERVERPROPERTY ('propertyname')
What's the "Collation" in SQL Server?

A collation is a configuration setting that determines how the database engine should treat character data at the server, database, or column level. SQL Server includes a large set of collations for handling the language and regional differences that come with supporting users and applications in different parts of the world.

For more details, check What is a SQL Server collation?

Get the Default Collation Name for the Server
  1. select SERVERPROPERTY('Collation') as 'Collation'.

Get the SQL Server Collation ID
  1. select SERVERPROPERTY('CollationID') as 'CollationID'.

Get the Collation Comparision Style
  1. select SERVERPROPERTY('ComparisonStyle') as 'ComparisonStyle'.

Get the Windows locale identifier (LCID) of the collation.
  1. select SERVERPROPERTY('LCID') as 'LCID'.

Get the SQL character set ID from the collation ID.
  1. select SERVERPROPERTY('SqlCharSet') as 'SqlCharSet'.

Get the SQL character set name from the collation.
  1. select SERVERPROPERTY('SqlCharSetName') as 'SqlCharSetName'.

Get the SQL sort order ID from the collation.
  1. select SERVERPROPERTY('SqlSortOrder') as 'SqlSortOrder'.

Get the SQL sort order name from the collation.
  1. select SERVERPROPERTY('SqlSortOrderName') as 'SqlSortOrderName'.

Get the SQL Server Collation using PowerShell

You can use Windows PowerShell to invoke SQL command on a reachable server within the network using Invoke-Sqlcmd cmdlet, as the following.
  • Open Windows PowerShell as Administrator
  • Type the Invoke-Sqlcmd with the below parameters.

    • -query: the SQL query that you need to run on the remote server.
    • -ServerInstance: the SQL server instance name.
    • -Username: the username that has sufficient permission to access and execute SQL query on the remote server.
    • -Password: the password of the elevated user.
  1. PS SQLSERVER:\> Invoke-Sqlcmd -query "select SERVERPROPERTY('Collation') as 'Collation'" -ServerInstance "epm\e
  2. pmdb" -Username sa -Password *****

Applies to

  • SQL Server 2008.
  • SQL Server 2012.
  • SQL Server 2014.
  • SQL Server 2016.
  • SQL Server 2017.

Reference

See also