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.

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.

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 TableName, 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 TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window

 387 total views,  3 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.