Data Definition Language (SQL DDL)

The information schema (INFORMATION_SCHEMA) is a standard representation of metadata in SQL. Historically, each developer of relational DBMS provided system tables that contained meta-information in particular names of tables, columns, data types, constraints, etc. Structure of system tables can be changed between versions of the product. However, the support of the information schema makes it possible to change the structure of system tables without changing the access to metadata. Another advantage of using INFORMATION_SCHEMA is that requests for metadata do not depend on the DBMS used.

Among the lead manufacturers, perhaps, only Oracle does not support INFORMATION_SCHEMA. To be fair, it should be said that Oracle provides the ability to use system views instead of directly accessing system tables, which also allows them to safely change the structure of system tables.

Here are some typical queries given in the standard style and for Oracle.

1. List of database tables (for current DB)

Standard

Console

Execute

SELECT table_name FROM information_schema.TABLES;

select table_name from information_schema.tables;

    Scheme

No scheme

Oracle

SELECT table_name FROM all_tables WHERE owner='SCHEMA NAME';

2. List of columns of the specified table (for example, PC)

Standard

Console

Execute

SELECT column_name FROM information_schema.COLUMNS WHERE table_name='PC';

select column_name from information_schema.columns where table_name='PC';

    Scheme

Oracle

SELECT column_name FROM all_tab_columns WHERE table_name='PC';

3. Data type and data size of the specified character column of the table (the model column of PC table)

Standard

Console

Execute

SELECT column_name, data_type, CHARACTER_MAXIMUM_LENGTH 
FROM information_schema.COLUMNS WHERE table_name='PC' AND column_name = 'model';

select column_name, data_type, CHARACTER_MAXIMUM_LENGTH from information_schema.columns where table_name='PC' and column_name = 'model';

    Scheme

Oracle

SELECT column_name,data_type,data_length FROM all_tab_columns 
WHERE table_name='PC' AND column_name='MODEL';

4. The name of the primary key constraint of PC table

Standard

Console

Execute

SELECT table_name,constraint_name  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE table_name='PC' AND CONSTRAINT_TYPE='Primary key';

select table_name,constraint_name  from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where table_name='PC' and CONSTRAINT_TYPE='Primary key';

    Scheme

Oracle

SELECT table_name,constraint_name FROM all_constraints 
WHERE table_name='PC' AND constraint_type='P';

5. List of columns that make up the primary key of the Utb table (composite key)

Standard

Console

Execute

SELECT table_name, column_name  
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE table_name='utb'
AND constraint_name=(SELECT constraint_name FROM INFORMATION_SCHEMA.table_constraints
WHERE table_name='utb' AND CONSTRAINT_TYPE='Primary key');

select table_name, column_name  from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where table_name='utb' and constraint_name=(select constraint_name from INFORMATION_SCHEMA.table_constraints where table_name='utb' and CONSTRAINT_TYPE='Primary key');

    Scheme

No scheme

Here, in the outer query, we get the columns which are mentioned in the constraints. After that we choose those of them that have constraint name matching the name of primary key constraint for the same table. In principle, condition table_name = 'utb' of the main query is redundant for getting the correct answer, because the constraint name is unique for the entire database. However, early filtering significantly speeds up the execution of the query.

Oracle

SELECT table_name, column_name FROM all_ind_columns WHERE table_name='UTB' 
AND index_name=(SELECT constraint_name FROM all_constraints 
WHERE table_name='UTB' AND constraint_type='P');