Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types, such as char and varchar, dictate the code page and corresponding characters that can be represented for that data type.
Different options you see in the collation name.
CS – case-sensitive
AI – accent-insensitive
KS – kana type-sensitive
WS – width-sensitive
SC – supplementary characters
UTF8 – Encoding standard
Collation levels
Setting collations are supported at the following levels of an instance of SQL Server:
Server-level collations
The default server collation is determined during SQL Server setup, and it becomes the default collation of the system databases and all user databases.
To query the server collation for an instance of SQL Server,
SELECT CONVERT(varchar, SERVERPROPERTY('collation'));
--return codepage
SELECT COLLATIONPROPERTY('Traditional_Spanish_CS_AS_KS_WS', 'CodePage');
To query the server for all available collations,
SELECT * FROM sys.fn_helpcollations();
Database-level collations
You can change the collation of a user database by using an ALTER DATABASE a statement that’s similar to the following:
ALTER DATABASE myDataBase COLLATE Latin1_General_CS_AI;
You can retrieve the current collation of a database by using a statement that’s similar to the following:
SELECT CONVERT (VARCHAR(50), DATABASEPROPERTYEX('database_name','collation'));
--for all database
select name, collation_name from sys.databases
Table-Level collations
You can change the collation of a column by using an ALTER TABLE a statement that’s similar to the following:
ALTER TABLE myTable ALTER COLUMN mycol NVARCHAR(10) COLLATE Latin1_General_CS_AI;
Expression-level collations are set when a statement is run, and they affect the way a result set is returned. This enables ORDER BY sort results to be locale-specific. To implement expression-level collations, use a COLLATE clause such as the following:
SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI; OR SELECT * FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.Col1 COLLATE Latin1_General_CS_AS = TABLE2.Col1 COLLATE Latin1_General_CS_AS
This T-SQL can help find the collation for a particular column:
SELECT OBJECT_NAME(OBJECT_ID), name ColumnName, collation_name AS ColumnCollation FROM sys.columns WHERE collation_name IS NOT NULL AND OBJECT_NAME(OBJECT_ID) = '?' AND name = '?' OR EXEC dbo.sp_MSforeachdb N' select TABLE_NAME, COLUMN_NAME, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, COLLATION_NAME from ?.INFORMATION_SCHEMA.COLUMNS where COLLATION_NAME is not null ';
Reference link
https://docs.microsoft.com/en-us/sql/t-sql/statements/collations?view=sql-server-ver15
