Emil Drkusic
the INFORMATION_SCHEMA database - the data model we'll use

Learn SQL: The INFORMATION_SCHEMA Database

February 7, 2020 by

The best way how to explain what the INFORMATION_SCHEMA database is would be – “This is the database about databases. It’s used to store details of other databases on the server”. What does that mean, how we can use it, and what we can do with this data is the topic of today’s article.

The Model

As always, we’ll start with the data model first. This is the same model we’ve used so far in this series, so I won’t describe it again.

the INFORMATION_SCHEMA database - the data model we'll use

Still, one thing is interesting. Today we won’t be interested in the data stored in tables, but rather how this model is described in the INFORMATION_SCHEMA database.

What is the INFORMATION_SCHEMA Database?

The INFORMATION_SCHEMA database is an ANSI standard set of views we can find in SQL Server, but also MySQL. Other database systems also have either exactly such or similar database implemented. It provides the read-only access to details related to databases and their objects (tables, constraints, procedures, views…) stored on the server.

You could easily use this data to:

  • Check what’s on the server and/or in the database
  • Check if everything is as expected (e.g. compared to the last time you’ve performed this check)
  • Automate processes and build some complex code (e.g. code generators – we’ll talk about this later)

Therefore, this database could prove to be very useful in some cases, especially if you’re in the DBA role

Listing All Databases

Maybe the first logical thing to do is to list all databases which are currently on our server. We can do it in a few ways. While these two are not directly related to the usage of the INFORMATION_SCHEMA and are SQL Server-specific, let’s look at them first.

list all databases on the server - using sys. or sp

You can easily notice that the first query returns many more details (several columns outside this pic) than the second query. It uses a SQL Server-specific sys object. While this works great, it’s very specific, so I’ll go into detail in a separate article. The second statement is the execution of the system stored procedure sp_databases which returns the predefined columns.

Using INFORMATION_SCHEMA to Access Tables Data

Since this database is an ANSI standard, the following queries should work in other DBMS systems as well. We’ll list all tables in the database we’ve selected and also all constraints. To do that, we’ll use the following queries:

list all tables and constraints in the selected database

The first important thing is that, after the keyword USE, we should define the database we want to run queries on. The result is expected. The first query lists all tables from our database, while the second query returns all constraints, we’ve defined when we created our database. In both of these, besides their name and the database schema they belong to, we can see many other details.

It’s important to notice that in constraints we also have the TABLE_NAME column, which tells us which table this constraint is related to. We’ll use that fact to relate tables INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.TABLE_CONSTRAINTS to create our custom query. Let’s take a look at the query as well as its’ result.

list all keys in the selected database

No doubt this query looks cool. Still, let’s comment on a few things. With this query, we’ve:

  • Again, we’ve pointed out which database we are using. This could have been avoided if you write <database_name> each time before INFORMATION_SCHEMA, e.g. our_first_database.INFORMATION_SCHEMA.TABLES.TABLE_NAME. I don’t prefer it that way
  • Joined two tables, in the same manner, we would join two “regular” database tables. This is good to know, but, as we’ll see later, you can join many things (system tables, subqueries) and not only “regular” tables
  • We have also ordered our result so we can easily notice all the constraints on each table

Maybe you’re asking yourself why would you do something like this. Well, with minor modifications to this query, you can easily count a number of keys in each table. Let’s do that.

the INFORMATION_SCHEMA database - count keys in tables

From the result, we can now easily notice the number of keys/constraints in all tables. This way we could find tables:

  • Without a primary key. This could be the result of an error in the design process
  • Without foreign keys. Tables without foreign keys should be only dictionaries or some kind of reporting table. In all other cases, we should have a foreign key
  • While UNIQUE shouldn’t be related to errors, in most cases we can expect that the table will have only 0 or 1 UNIQUE values. In case there is more, we could check why is that

Queries like this one could be a part of controls checking is everything ok with your database. You could complicate things even more and use this query as a subquery for a more complex query that will automatically test predefined errors/alerts/warnings.

The INFORMATION_SCHEMA Tables

It would be hard to try out every single table and show what it returns. At least, that would be hard to put into one readable article. I strongly encourage you to play with the INFORMATION_SCHEMA database and explore what is where. The only thing I’ll do here is to list all the tables (views) you have at disposal. They are:

  • CHECK_CONSTRAINTS – details related to each CHECK constraint
  • COLUMN_DOMAIN_USAGE – details related to columns that have an alias data type
  • COLUMN_PRIVILEGES – columns privileges granted to or granted by the current user
  • COLUMNS – columns from the current database
  • CONSTRAINT_COLUMN_USAGE – details about column-related constraints
  • CONSTRAINT_TABLE_USAGE – details about table-related constraints
  • DOMAIN_CONSTRAINTS – details related to alias data types and rules related to them (accessible by this user)
  • DOMAINS – alias data type details (accessible by this user)
  • KEY_COLUMN_USAGE – details returned if the column is related with keys or not
  • PARAMETERS – details related to each parameter related to user-defined functions and procedures accessible by this user
  • REFERENTIAL_CONSTRAINTS – details about foreign keys
  • ROUTINES –details related to routines (functions & procedures) stored in the database
  • ROUTINE_COLUMNS – one row for each column returned by the table-valued function
  • SCHEMATA – details related to schemas in the current database
  • TABLE_CONSTRAINTS – details related to table constraints in the current database
  • TABLE_PRIVILEGES –table privileges granted to or granted by the current user
  • TABLES –details related to tables stored in the database
  • VIEW_COLUMN_USAGE – details about columns used in the view definition
  • VIEW_TABLE_USAGE – details about the tables used in the view definition
  • VIEWS – details related to views stored in the database

Conclusion

Querying the INFORMATION_SCHEMA database provides you with a lot of options on how to control the changes in the structure of your database as well to implement some level of automation on the database layer. In order to achieve these two, you should follow some rules, like naming convention, internal data modeling rules, etc. You could also exploit it to document your database. Later in this series, we’ll talk more about that.

Table of contents

Learn SQL: CREATE DATABASE & CREATE TABLE Operations
Learn SQL: INSERT INTO TABLE
Learn SQL: Primary Key
Learn SQL: Foreign Key
Learn SQL: SELECT statement
Learn SQL: INNER JOIN vs LEFT JOIN
Learn SQL: SQL Scripts
Learn SQL: Types of relations
Learn SQL: Join multiple tables
Learn SQL: Aggregate Functions
Learn SQL: How to Write a Complex SELECT Query?
Learn SQL: The INFORMATION_SCHEMA Database
Learn SQL: SQL Data Types
Learn SQL: Set Theory
Learn SQL: User-Defined Functions
Learn SQL: User-Defined Stored Procedures
Learn SQL: SQL Views
Learn SQL: SQL Triggers
Learn SQL: Practice SQL Queries
Learn SQL: SQL Query examples
Learn SQL: Create a report manually using SQL queries
Learn SQL: SQL Server date and time functions
Learn SQL: Create SQL Server reports using date and time functions
Learn SQL: SQL Server Pivot Tables
Learn SQL: SQL Server export to Excel
Learn SQL: Intro to SQL Server loops
Learn SQL: SQL Server Cursors
Learn SQL: SQL Best Practices for Deleting and Updating data
Learn SQL: Naming Conventions
Learn SQL: SQL-Related Jobs
Learn SQL: Non-Equi Joins in SQL Server

Emil Drkusic
Latest posts by Emil Drkusic (see all)
SQL commands, T-SQL

About Emil Drkusic

Emil is a database professional with 10+ years of experience in everything related to databases. During the years, he worked in the IT and finance industry and now works as a freelancer. His past and present engagements vary from database design and coding to teaching, consulting, and writing about databases. Also not to forget, BI, creating algorithms, chess, philately, 2 dogs, 2 cats, 1 wife, 1 baby... You can find him on LinkedIn View all posts by Emil Drkusic

6,288 Views