Obtaining Meta-data about Database Table Columns

Sep 22, 2020 by Robert Gravelle

Certain relational databases, including MySQL and SQL Server, have an INFORMATION_SCHEMA system database. It contains database metadata, such as the names of databases, tables, the column data types, and even access privileges. It’s also sometimes referred to as the data dictionary or system catalog. Regardless of how you refer to it, the INFORMATION_SCHEMA database is the ideal place to obtain details about table columns. In today’s blog, we’ll use the INFORMATION_SCHEMA database to find out whether or not a column exists and how many columns a particular table has.

Being a system table, you won’t be able to see the INFORMATION_SCHEMA database unless you explicitly tell Navicat to show it. To do that, add the INFORMATION_SCHEMA database to the Databases list defined within a database connection:

That allows us to open the Columns table in the Table Designer or Viewer:

The sheer number of columns should give you some idea as to what types of information we can obtain from the Columns table.

Note: the INFORMATION_SCHEMA is a read-only database, so you can’t make any changes to its structure or contents.

The Columns table may be queried like any other to look up information about table columns. Here’s the basic syntax to do so:

SELECT count(*) AS anyName FROM information_schema.columns 
WHERE [table_schema = 'yourSchemaName' AND] 
table_name = 'yourTableName'; 

The table_schema is the database in which the table resides. It’s not crucial to the query, but in cases where you have more than one database with the same column name, it filters results to that particular database table. In a situation where you maintain multiple copies of the same database, the column count will be for of all tables with the same name.

For instance, I have four copies of the Sakila database:

As a result, when I run the query without the table_schema, I get a column count of 51, which is on the high side!

Specifying the table_schema in a more accurate column count of 12:

If we now open the film table in the Table Designer, we can confirm that 12 columns is correct:

In a dynamic application, you may want to look up information about a column, including whether or not it exists. Here’s a query that lists every instance of the “title” column, along with meta-data about each of them, including which schema and table it belongs to, as well as details such as the default value, data type, and maximum length:

In today’s blog, we learned how to utilize the INFORMATION_SCHEMA database to find out whether or not a column exists and how many columns a particular table has.

Interested in Navicat Premium? You can try it for 14 days completely free of charge for evaluation purposes!