Here are five ways to see whether a table contains a generated column in MariaDB.

The SHOW COLUMNS Statement

SHOW COLUMNS FROM Employees;

Result:

Field	        Type	        Null	Key	Default	Extra
--------------- --------------- ------- ------- ------- ----------------
Id	        int(11)	        NO      PRI	NULL	
FirstName	varchar(50)	YES		NULL	
LastName	varchar(50)	YES		NULL	
FullName	varchar(101)	YES		NULL	STORED GENERATED

In this example, the FullName column is a generated column. We know this because it has GENERATED in the EXTRA column. More specifically, it has VIRTUAL GENERATED, which means that it’s a virtual generated column (as opposed to a stored generated column).

It’s also possible to add a WHERE clause to the SHOW COLUMNS statement to narrow it down to a specific column or columns:

SHOW COLUMNS FROM Employees
WHERE Field = 'FullName';

Result:

Field	        Type	        Null	Key	Default	Extra
--------------- --------------- ------- ------- ------- ----------------
FullName	varchar(101)	YES		NULL	STORED GENERATED

The DESCRIBE / DESC Statement

The DESCRIBE statement is a shortcut for the SHOW COLUMNS FROM syntax:

DESCRIBE Employees;

Result:

Field	        Type	        Null	Key	Default	Extra
--------------- --------------- ------- ------- ------- ----------------
Id	        int(11)	        NO      PRI	NULL	
FirstName	varchar(50)	YES		NULL	
LastName	varchar(50)	YES		NULL	
FullName	varchar(101)	YES		NULL	STORED GENERATED

To return information about just one column, simply include it after the table name:

DESCRIBE Employees FullName;

Result:

Field	        Type	        Null	Key	Default	Extra
--------------- --------------- ------- ------- ------- ----------------
FullName	varchar(101)	YES		NULL	STORED GENERATED

The COLUMNS Information Schema Table

You can query the information_schema.columns table. This table contains information about columns in each table on the server. It includes a column called is_generated that will tell us if a column is generated.

Example:

SELECT 
  column_name, 
  extra,
  is_generated,
  generation_expression
FROM information_schema.columns
WHERE table_name = 'Employees';

Result:

column_name	extra	                is_generated   generation_expression
--------------- ----------------------- -------------- ----------------------------------
Id		                        NEVER	       NULL
FirstName		                NEVER	       NULL
LastName		                NEVER	       NULL
FullName        STORED GENERATED	ALWAYS	       concat(`FirstName`,' ',`LastName`)

This example also includes the extra and generation_expression columns. The extra column is a MariaDB/MySQL extension that returns extra information about the column. The generation_expression column returns the expression that the generated uses to generate its value. If it’s not a generated column, this column is NULL.

The SHOW CREATE TABLE Statement

Another way to do it is with the SHOW CREATE TABLE statement:

SHOW CREATE TABLE Employees;

Result:

Table	        Create Table
--------------- -----------------------------------------------------------------------------------
Employees	CREATE TABLE `Employees` (
                `Id` int(11) NOT NULL,
                `FirstName` varchar(50) DEFAULT NULL,
                `LastName` varchar(50) DEFAULT NULL,
                `FullName` varchar(101) GENERATED ALWAYS AS (concat(`FirstName`,' ',`LastName`)) STORED,
                PRIMARY KEY (`Id`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The SHOW CREATE TABLE statement shows the CREATE TABLE statement that created the given table. Therefore, when we use this, we’ll see the definition of each column in the table – including any generated columns.

The mariadb-show Client

Another way to do it is with the mariadb-show client. This is a separate client, so you run this from the command line (i.e. not from within MariaDB itself).

This client is the same as the mysqlshow client for MySQL. In fact, we can use mysqlshow instead of mariadb-show if we prefer.

We can check the above table by opening the command line (e.g. open a new Terminal window) and entering the following:

mariadb-show MyDB Employees

This could also be run like this:

mysqlshow MyDB Employees

This returns various columns that contain information about the Employees table, including the Extra column that we’ve seen in the previous examples.