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
