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.)
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
SELECT * FROM sys.objects;
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
USER_TABLE or where the
SELECT * FROM sys.objects WHERE type = 'U';
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;
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
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.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;
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;
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
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';
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
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;
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
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;
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.
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?