Introduction

In this lesson, you will learn about how to query database meta data.

Sample Database

We will use an in-memory SQLite database for this lesson. At the end, we will demonstrate that the same commands also work for MySQL (and all other standard relational database management systems).

create table lessons (
  lname text not null,
  llength integer not null
);

Let’s add a few rows of data to the table so we can see how SQLite assigns a row id.

insert into lessons values
  ('Intro C++', 90),
  ('Intro Java', 90),
  ('SQL Joins', 60)

Query Table Structure

To find the structure of a table in SQLite, one can either execute the .schema or .schema table_name for a specific table from the SQLite console or issue a PRAGMA command from a program as shown below.

PRAGMA table_info(lessons);
Table 1: 2 records
cid name type notnull dflt_value pk
0 lname TEXT 1 NA 0
1 llength INTEGER 1 NA 0

It returns the schema of a table with column names, column attributes, whether it can be null or not, a default value if present, and whether the column is part of the primary key.

The SQL command for querying the structure of a table in in MySQL it is DESC.

Databases store schemas in tables, so an alternative is to query the tables that contain the table information, i.e., the meta data (data about the data) of the database. In SQLite_, the table sqlite_schema contains information about tables. This approach is illustrated below.

select * 
  from sqlite_schema
 where name = 'lessons';
Table 2: 1 records
type name tbl_name rootpage sql
table lessons lessons 2 CREATE TABLE lessons (

lname text not null, llength integer not null ) |

If we want the SQL that was used to create the table, we can just get that column. Rather than using a {sql} block, we will use an R block so that we can capture the SQL statement used to create the table.

rs <- dbGetQuery(dbcon, paste0(
        "select sql",
        "  from sqlite_schema",
        " where name = 'lessons'"))
        
cat(rs[[1]])
CREATE TABLE lessons (
  lname text not null,
  llength integer not null
)

List Schema

To list the names of all tables in the database (but omit the tables that hold the schema), use the SQL statement below.

select name
  from sqlite_schema
 where `type` ='table' 
   and name not like 'sqlite_%';
Table 3: 1 records
name
lessons

A similar process can be used to find all index defintions, triggers, and constraints.

R DBI Convenience Functions

R provides a collection of functions to query the database meta information, such as dbListTables(). For more information, see Lesson 6.301 Working with Databases in R.

Conclusion

Database meta information is stored in tables and can be queried, making application code independent from hard-coded schema definitions.

Files & Resources

All Files for Lesson 70.810

References

None.

Errata

Let us know.