How to Query Metadata in SQL

Learning how to query metadata in SQL will help you to navigate your databases more efficiently. Metadata is data about your database structure. This includes information about all of the objects in your database – tables, columns, procedures, functions, and more.

Knowing how to query this data can help you to find things quickly, especially in large databases. For example, if you have a lot of tables and you need to see every table with a column named user_id then you can query the database’s metadata to find that out. Let’s take a look at how we would accomplish basic metadata queries in each of the three database platforms: Microsoft SQL Server, PostgreSQL, and MySQL.

For this tutorial, we will focus on querying metadata about tables and columns. (Note: you can find the table definition we use for our sales_records table used here in the Github repository on the Complete Guide to SQL Math Operators page.)

Table of Contents

Query Metadata in SQL Server

Microsoft SQL Server provides the sys schema specifically for the purpose of tracking metadata. Within this schema, you will find tables that store database objects such as tables and stored procedures. Other tables allow you to get even more detail about these database objects. But it’s important to start with the understanding that the objects table is usually the starting point.

Get a List of Tables in SQL Server Database

Let’s start by getting a list of tables in our SQL Server database. We’re going to begin by querying the sys.objects table.

SELECT *
FROM sys.objects;

Query metadata in SQL Server - sys.objects results — Figure 1: Sample results from a query of all sys.objects records

This will return a list of every “object” in the database. Each object has a name, an object ID, a type, and a type description along with several other fields. Our results above show records with a type of SYSTEM_TABLE. These are tables that are internal to MS SQL Server. What if we only want to see the tables that we’ve created?

The simple way to accomplish this is to filter our query to only see records where the type_desc is USER_TABLE or where the type is U.

SELECT *
FROM sys.objects
WHERE type = 'U';

Figure 2: Query results for a list of user tables in SQL Server

You can get details on all of the fields in the sys.objects table and the definitions of the different types of records stored there from the Microsoft online documentation. This table can be used to get a list of all views, stored procedures, functions, primary and foreign keys, and other types of objects.

Get a List of Table Columns in SQL Server

Now let’s talk about how to get a list of a table’s columns in SQL Server. This is an easy task using the sys.columns table. We will join the columns table to the objects table using the object_id key so that we can get only the columns for one particular table.

SELECT *
FROM sys.columns c
INNER JOIN sys.objects o ON o.object_id = c.object_id
WHERE o.name = 'sales_records';

When we run this query, we get a lot of fields returned. The columns table keeps track of a lot of information about each column. Let’s refine our query to give us a little more precise information. We want to see the name of the table, the name of the column, the data type of the column, and whether the column allows NULL values. We will include the column ID so that we can sort based on the order of the columns in the table.

SELECT o.name AS table_name,
       c.column_id,
       c.name AS column_name,
       t.name,
       c.precision,
       c.scale,
       c.max_length,
       c.is_nullable
FROM sys.columns c
INNER JOIN sys.objects o ON o.object_id = c.object_id
INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE o.name = 'sales_records'
ORDER BY c.column_id;

Figure 3: Results of column selection query in SQL Server

We now have the important information about all the columns in our sales_records table in one place. You can modify the query WHERE clause to as needed. You can get all columns for one table. Or you can get all the columns in a database with a certain name.

Using the Information Schema in MS SQL Server

In addition to the various tables available within the sys schema, such as objects and columns, MS SQL Server provides the INFORMATION_SCHEMA as a dedicated schema with different views for querying metadata easily.

We can accomplish the same table and column queries with INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS. In fact, it is somewhat easier to use because we don’t have to apply filters to exclude system-only objects.

Here is a query to get a list of user-defined tables in our SQL Server database.

SELECT *
FROM INFORMATION_SCHEMA.TABLES;

Figure 4: Querying MS SQL tables using INFORMATION_SCHEMA.TABLES

Similarly, we can use INFORMATION_SCHEMA.COLUMNS to get a list of all columns and the related data elements. Because this is a view, we can query it just like a table, including applying a WHERE clause to filter our results.

SELECT TABLE_NAME,
       COLUMN_NAME,
       ORDINAL_POSITION,
       DATA_TYPE,
       NUMERIC_PRECISION,
       NUMERIC_SCALE,
       CHARACTER_MAXIMUM_LENGTH,
       IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'sales_records';

Query Metadata in MySQL

MySQL gives us similar tools to be able to query metadata. In MySQL, we will primarily use the INFORMATION_SCHEMA to get information about tables, columns, and other database objects.

You can find complete documentation for the MySQL INFORMATION_SCHEMA in the official MySQL documentation.

Get a List of Tables in MySQL Database

Getting a list of tables in a MySQL database is easy to do using the INFORMATION_SCHEMA.TABLES table. This table will list all types of tables including system views and base tables.

SELECT *
FROM information_schema.TABLES;

Figure 5: Retrieving a list of tables in MySQL database using INFORMATION_SCHEMA.TABLES

One of the benefits that MySQL gives us in this view is that we can see how many rows are in each table using the TABLE_ROWS column. If we want to see only tables that we’ve created, we can modify our query to include only records where TABLE_TYPE = 'BASE TABLE' AND ENGINE != 'PERFORMANCE_SCHEMA'. The query below will return only the sales_records entry in our database.

SELECT *
FROM information_schema.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
    AND ENGINE != 'PERFORMANCE_SCHEMA';

Get a List of Table Columns in MySQL

MySQL makes it very easy to get a list of columns for a specific table. All of the information is contained within the INFORMATION_SCHEMA.COLUMNS table. This includes the table name, the data type, ordinal position, and whether the column allows for NULL values. Our query below demonstrates how to get this information for our sales_records table.

SELECT TABLE_NAME,
       COLUMN_NAME,
       ORDINAL_POSITION,
       DATA_TYPE,
       NUMERIC_PRECISION,
       NUMERIC_SCALE,
       CHARACTER_MAXIMUM_LENGTH,
       COLUMN_TYPE,
       IS_NULLABLE
FROM information_schema.COLUMNS
WHERE TABLE_NAME = 'sales_records';

Figure 6: Querying all columns in a MySQL Table

One of the added benefits in MySQL is that you can use the COLUMN_TYPE to see the definition of the column. At the same time, the NUMERIC_PRECISION, NUMERIC_SCALE, and CHARACTER_MAXIMUM_LENGTH fields can be combined with the DATA_TYPE field to get the components of the column definition.

Notice that our MySQL query is very similar to the MS SQL query against the INFORMATION_SCHEMA. The primary difference is that MySQL provides some different columns. But this makes it easy to move between the database platforms with only minor adjustments to your queries.

Query Metadata in PostgreSQL

PostgreSQL exposes an INFORMATION_SCHEMA to us for use in querying data about the objects in our database. We can use this to get lists of tables, views, columns, functions, sequences and other objects captured in our database.

You can get complete documentation on the PostgreSQL information schema from the official PostgreSQL documentation.

Get a List of Tables in PostgreSQL Database

We can start by getting a list of tables in the PostgreSQL database. We will use information_schema.tables to do so. In order to see only the tables we have created, we will filter this on table_schema = 'public' AND table_type = 'BASE TABLE'.

SELECT *
FROM information_schema.tables;

Figure 7: Retrieving a list of tables in the PostgreSQL database public schema

Get a List of Table Columns in PostgreSQL

Similarly, obtained a list of all the columns for a particular table is very simple in PostgreSQL. To do this, we will query the information_schema.columns view.

SELECT table_name,
        column_name,
        ordinal_position,
        data_type,
        numeric_precision,
        numeric_scale,
        character_maximum_length,
        is_nullable
FROM information_schema.columns
WHERE table_name = 'sales_records'
ORDER BY ordinal_position;

Figure 8: Getting a list of columns for a specific table in PostgreSQL

We can see here that the most of the same fields are available to us in PostgreSQL that we saw in the MS SQL Server and MySQL information schemas. Again, this makes it easy to move between different database platforms and be able to navigate the metadata within our databases consistently.