Each of the major SQL products has a system table or view that keeps track of the tables in the database. In DB2, for example, this information is provided by a system catalog view named SYSCAT.TABLES. (All of the DB2 system catalog views are part of a schema named SYSCAT, so they all have qualified table/view names of the form SYSCAT. xxx.)
Table 16-2 shows some of the columns of the SYSCAT.TABLES view. It contains one row for each table, view, or alias defined in the database. The information in this view is typical of that provided by the corresponding views in other major DBMS products.
You can use queries like the following examples to find out information about the tables in a DB2 database. Similar queries, using different table and column names, can be used to obtain the same information from other DBMS brands.
List the names and owners of all tables in the database.
SELECT DEFINER, TABNAME
FROM SYSCAT.TABLES
WHERE TYPE = ‘T’
List the names of all tables, views, and aliases in the database.
SELECT TABNAME
FROM SYSCAT.TABLES
List the names and creation times of only my tables.
SELECT TABNAME, CREATE_TIME
FROM SYSCAT.TABLES
WHERE TYPE = ‘T’
AND DEFINER = USER
In an Oracle database, a pair of system views named USER_TABLES and ALL_TABLES perform a similar function to the DB2 SYSCAT.TABLES view. The USER_TABLES view contains one row for each database table that is owned by the current user. The ALL_TABLES view contains one row for each table to which the current user has access. The ALL_TABLES view thus will include all of the rows from USER_TABLES, plus additional rows representing tables owned by other users to which the current user has been granted at least one of the access privileges. Here is a typical query against these Oracle system catalog views:
List the names and owners of all tables to which I have access.
SELECT TABLE_NAME, OWNER
FROM ALL_TABLES
The SQL Server equivalent of the DB2 SYSCAT.TABLES view is a system table named SYSOBJECTS, described in Table 16-3. The SYSOBJECTS table stores information about SQL Server tables and views and other SQL Server objects such as stored procedures, rules, and triggers. Note also how the SYSOBJECTS table uses an internal ID number instead of a name to identify the table owner.
The Informix Universal Server system table that gives information about tables is named SYSTABLES. Like the DB2 catalog, it contains information only about tables, views, and aliases; other database objects are described in other system tables. Here is a typical query against this Informix system table:
List the name, owner, and creation date of all tables in the database.
SELECT TABNAME, OWNER, CREATED
FROM SYSTABLES
WHERE TABTYPE = ‘T’
As these examples show, the queries to obtain table information have a similar structure across DBMS brands. However, the specific names of the system table(s) or view(s) containing the information, and the relevant columns, vary considerably across brands.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.