Chapter 5. Metadata Queries
This chapter presents recipes that allow you to find information about a given schema. For example, you may wish 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. There is, however, far more information available than the recipes in this chapter can show. Consult your RDBMSs documentation for the complete list of catalog or data dictionary tables/views.
Tip
For purposes of demonstration, all the recipes in this chapter assume the schema name SMEAGOL.
5.1. Listing Tables in a Schema
Problem
You want to see a list 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 view) containing a row for each table in the database.
DB2
Query SYSCAT.TABLES:
1 select tabname 2 from syscat.tables 3 where tabschema = 'SMEAGOL'
Oracle
Query SYS.ALL_TABLES:
select table_name from all_tables where owner = 'SMEAGOL'
PostgreSQL, MySQL, and SQL Server
Query INFORMATION_SCHEMA.TABLES:
1 select table_name 2 from information_schema.tables 3 where table_schema = 'SMEAGOL'
Discussion
In a delightfully circular manner, databases expose information about themselves through the very mechanisms that you create for your own applications: ...
Get SQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.