How to Show the Collation of a Column in MySQL

Posted on by Ian

This page contains three ways of returning the collation of a column in MySQL.

Running the following query is the quickest way to return the collation of a column. In particular, it returns information about each column in a given table. This includes the collation information.

SHOW FULL COLUMNS FROM Artists;

That results in a lot of columns being returned with all sorts of information about the column, including the collation. To reduce the number of columns returned, see below.
Continue reading →

How to Show the Collation of a Table in MySQL

Posted on by Ian

Here are two ways to return the collation of a table in MySQL.

The quickest way to return the collation of a given table in MySQL is to run the following statement:

SHOW TABLE STATUS LIKE '%Artists%';

Running this statement will return a whole bunch of columns that provide information about any matching table/s. One of these columns is called Collation, and it provides the collation of all matching tables.

Of course, you’ll need to replace %Artists% with your own table name. And you can omit the percentage signs if you don’t think they’re needed. This statement also accepts other clauses, such as FROM, WHERE, and IN, so this gives you some options when building your statement.
Continue reading →

How to Find the Collation in MySQL

Posted on by Ian

In MySQL, collation can be applied at many levels. It can be applied at the server level, the connection level, the database level, the table level, and even at the column level. You can also specify a collation in your queries that will override any collation that has been applied at the database, table, or column levels.

Here’s how to find out what collation is being applied at each of these levels.

Shortcut for Connection, Server, and Database Collation

The quickest way to get collation information for the connection, server, and database is to use the following statement. This statement returns all system variables starting with collation:

SHOW VARIABLES LIKE 'collation%';

This returns the collation for the server, connection, and database. Like this:

+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8_general_ci    |
| collation_server     | utf8mb4_0900_ai_ci |
+----------------------+--------------------+

You can also return each of these system variables separately if required. See below for instructions on how to do that.
Continue reading →

How to Find the Collation in SQL Server (T-SQL)

Posted on by Ian

Collation can be specified at the server level, database level, column level, expression level, and the identifier level. A different method is required for each of these.

Server Level Collation

Here’s how to find the server level collation:

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';

This returns the collation like this:

Server Collation
----------------------------
SQL_Latin1_General_CP1_CI_AS

Continue reading →

How to Check your SQL Server Version

Posted on by Ian

SQL Server, or more specifically, Transact-SQL, includes a number of built in functions, including the @@version scalar function. The @@version function is a configuration function, which returns system and build information for the current installation of SQL Server. You can run this function at any time to find out which version of SQL Server you’re using.

Here’s how:

SELECT @@version;

When running that in a command line interface, you might see results that look like this:

Microsoft SQL Server 2017 (RTM-CU6) (KB4101464) - 14.0.3025.34 (X64) 
	Apr  9 2018 18:00:41 
	Copyright (C) 2017 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 16.04.4 LTS)

1 row(s) returned

Executed in 1 ms

Continue reading →

How to Return a List of Available Collations in PostgreSQL

Posted on by Ian

In PostgreSQL, we can use the pg_collation catalog to get a list of the available collations. Another way to do it is to use the \dOS command.

Using the first method, we can run the following statement to return a list of available collations in PostgreSQL:

SELECT * 
FROM pg_collation;

These collations are mappings from an SQL name to operating system locale categories.

Continue reading →