SQL Cookbook, 2nd Edition

Chapter 5. Metadata Queries

This chapter presents recipes that allow you to find information about a given schema. For example, you may want to know what tables you’ve created or which foreign keys are not indexed. All of the RDBMSs in this book provide tables and views for obtaining such data. The recipes in this chapter will get you started on gleaning information from those tables and views.

Although at a high level the strategy of storing metadata in tables and views within the RDBMS is common, the ultimate implementation is not standardized to the same degree as most of the SQL language features covered in this book. Therefore, compared to other chapters, in this chapter having a different solution for each RDBMS is far more common.

The following is selection of the most common schema queries written for each of the RDMSs covered in the book. There is far more information available than the recipes in this chapter can show. Consult your RDBMS’s documentation for the complete list of catalog or data dictionary tables/views when you need to go beyond what’s presented here.

Tip

For the purposes of demonstration, all of the recipes in this chapter assume there is a schema named SMEAGOL.

5.1 Listing Tables in a Schema

Problem

You want to see a list of all the tables you’ve created in a given schema.

Solution

The solutions that follow all assume you are working with the SMEAGOL schema. The basic approach to a solution is the same for all RDBMSs: you query a system table (or ...