View Collation Information in SQL Server

Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data.

You can easily find SQL collation at the server, database and column level using SQL Server System Catalog views, or Metadata functions .

Fetch the list of all supported collations in SQL Server

Using SQL Server fn_helpcollations metadata function you can see list of all supported collations in SQL Server.

SELECT 
Name,
Description 
FROM sys.fn_helpcollations()

View a collation Information of SQL Server

Using a SERVERPROPERTY metadata function

You can get a default collation name of SQL Server Instance using SERVERPROPERTY metadata function as shown below.

SELECT SERVERPROPERTY('collation') AS ServeCollation

View collation information of Database

Using a sys.databases system catalog view

You can see the collation information of any database using sys.databasees system catalog view.

SELECT Name, collation_name AS DatabaseCollation 
FROM sys.databases

Using a DATABASEPROPERTYEX metadata function

You can also use DATABASEPROPERTYEX metadata function to see the collation information of any specified database in SQL Server.

SELECT 
DATABASEPROPERTYEX('AdventureWorks2017','collation') AS DatabaseCollation

View Collation Information of column in table

To see the collation on columns in tables, you can use  following T-SQL query.

SELECT tbl.name TableName, col.name ColumnName, 
col.collation_name AS ColumnCollation 
FROM sys.columns col 
INNER JOIN sys.tables tbl ON col.object_id = tbl.object_id

Collation information using SQL Server Management Studio

View Collation on SQL Server:

To see the collation on SQL Server instance using SQL Server Management Studio, right click on SQL Server Instance, then select Properties from context menu.

Once you click on Properties, a server Properties dialog box open.

In General tab, you can see Server Collation.

[Also Read: Change the column collation to case sensitive]

View collation on Database

To see the collation of any database using SQL Server Management, right click on Database – AdventureWorks2017, then select Properties.

Once you click on Properties, a Database Properties dialog box open.

In General tab, you can see Database Collation.

View collation on Column

To see the collation on column, right click on column name and select properties from context menu.

Lets see the collation of column Name in table Location.

You can see, once you click on Properties, a Column Properties dialog box opens, there you can see the column collation information.

Also Read..

Change the column collation to case sensitive

SQL Basics Tutorial	SQL Advance Tutorial	SSRS	Interview Q & A
SQL Create table	SQL Server Stored Procedure	Create a New SSRS Project	List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLE	SQL Server Merge	Create a Shared Data Source in SSRS	SQL Server Question & Answer Quiz
SQL Drop	SQL Server Pivot	Create a SSRS Tabular Report / Detail Report
..... More	.... More	....More
Power BI Tutorial	Azure Tutorial	Python Tutorial	SQL Server Tips & Tricks
Download and Install Power BI Desktop	Create an Azure storage account	Learn Python & ML Step by step	Enable Dark theme in SQL Server Management studio
Connect Power BI to SQL Server	Upload files to Azure storage container		SQL Server Template Explorer
Create Report ToolTip Pages in Power BI	Create Azure SQL Database Server		Displaying line numbers in Query Editor Window
....More	....More		....More

 7,332 total views,  3 views today