5. Metadata Queries

Refer to the RDBMS’s documentation for the complete list of catalog or data dictionary tables/views for more details.

Oracle Metadata Tables

Metadata Table	Description
ALL_TABLES	Contains information about all tables accessible to the current user.
ALL_TAB_COLUMNS	Contains information about all columns in all tables accessible to the current user.
ALL_IND_COLUMNS	Contains information about all columns in indexes on all tables accessible to the current user.
ALL_CONSTRAINTS	Contains information about all constraints on tables accessible to the current user.
ALL_CONS_COLUMNS	Contains information about columns that are used in constraints on tables accessible to the current user.
USER_TABLES	Contains information about all tables owned by the current user.
USER_CONSTRAINTS	Contains information about all constraints on tables owned by the current user.
DICTIONARY	Contains descriptions of all dictionary tables and views.
DICT_COLUMNS	Contains information about columns of all dictionary tables and views.
ALL_VIEWS	Shows information about Views to which the user has access, regardless of ownership
USER_ VIEWS	Shows information about Views owned by the current user.

Listing Tables in a Schema

We want to see a list of all the tables we have created in a given schema

-- Oracle
select table_name from all_tables
where owner = 'PRANAY'

-- PostgreSQL, MySQL, and SQL Server 
select table_name from information_schema.tables
where table_schema = 'PRANAY'

Listing a Table’s Columns

We want to list the columns in a table, along with their data types, and their position in the table they are in.

-- Oracle
select column_name, data_type, column_id from all_tab_columns
where owner = 'PRANAY' and table_name = 'EMP'

-- PostgreSQL, MySQL, and SQL Server
select column_name, data_type, ordinal_position from information_schema.columns
where table_schema = 'PRANAY' and table_name = 'EMP'

Listing Indexed Columns for a Table

We want to list indexes, their columns, and the column position (if available) in the index for a given table.

-- Oracle
select table_name, index_name, column_name, column_position from sys.all_ind_columns
where table_name = 'EMP' and table_owner = 'PRANAY'

-- PostgreSQL
select a.tablename,a.indexname,b.column_name from pg_catalog.pg_indexes a, information_schema.columns b
where a.schemaname = 'PRANAY' and a.tablename = b.table_name

-- MySQL
show index from emp

Listing Constraints on a Table

We want to list the constraints defined for a table in some schema and the columns they are defined on.

-- Oracle
select a.table_name,
    a.constraint_name,
    b.column_name,
    a.constraint_type
from all_constraints a, all_cons_columns b
where a.table_name = 'EMP' and a.owner = 'PRANAY' and a.table_name = b.table_name
and a.owner = b.owner and a.constraint_name = b.constraint_name

-- PostgreSQL, MySQL, and SQL Server
select a.table_name,
    a.constraint_name,
    b.column_name,
    a.constraint_type
from information_schema.table_constraints a, information_schema.key_column_usage b
where a.table_name = 'EMP' and a.table_schema = 'PRANAY'
and a.table_name = b.table_name and a.table_schema = b.table_schema
and a.constraint_name = b.constraint_name

Listing Foreign Keys Without Corresponding Indexes

We want to list tables that have foreign key columns that are not indexed.

-- Oracle
select a.table_name, a.constraint_name, a.column_name, c.index_name
from all_cons_columns a, all_constraints b, all_ind_columns c
where a.table_name = 'EMP'
and a.owner = 'PRANAY'
and b.constraint_type = 'R'
and a.owner = b.owner
and a.table_name = b.table_name
and a.constraint_name = b.constraint_name
and a.owner = c.table_owner (+)
and a.table_name = c.table_name (+)
and a.column_name = c.column_name (+)
and c.index_name is null

Using SQL to Generate SQL

We want to create dynamic SQL statements. We need to accomplish three tasks in particular: count the number of rows in tables, disable foreign key constraints defined on tables, and generate insert scripts from the data in tables.

-- Generate SQL to count all the rows in all your tables
select 'select count(*) from '||table_name||';' cnts from user_tables;

CNTS
----------------------------------------
select count(*) from ANT;
select count(*) from BONUS;
select count(*) from DEMO1;
select count(*) from DEMO2;

-- Disable foreign keys from all tables
select 'alter table '||table_name||' disable constraint '||constraint_name||';' cons
from user_constraints where constraint_type = 'R';

CONS
------------------------------------------------
alter table ANT disable constraint ANT_FK;
alter table BONUS disable constraint BONUS_FK;
alter table DEMO1 disable constraint DEMO1_FK;
alter table DEMO2 disable constraint DEMO2_FK;

-- Generate an insert script from some columns in table EMP
select 'insert into emp(empno,ename,hiredate) '||chr(10)||'values( '||empno||','||''''||ename
||''',to_date('||''''||hiredate||''') );' inserts
from emp where deptno = 10;

INSERTS
--------------------------------------------------
insert into emp(empno,ename,hiredate)
values( 7782,'CLARK',to_date('09-JUN-2006 00:00:00') );
insert into emp(empno,ename,hiredate)
values( 7839,'KING',to_date('17-NOV-2006 00:00:00') );

Describing the Data Dictionary Views in an Oracle Database

Oracle maintain a robust set of data dictionary views and also there are data dictionary views to document the data dictionary views.

select table_name, comments
from dictionary
order by table_name;

TABLE_NAME                     COMMENTS
------------------------------ --------------------------------------------
ALL_ALL_TABLES                 Description of all object and relational tables accessible to the user
ALL_APPLY                      Details about each apply process that dequeues from the queue visible to the current user

select column_name, comments
from dict_columns
where table_name = 'ALL_TAB_COLUMNS';
COLUMN_NAME                     COMMENTS
------------------------------- --------------------------------------------
OWNER
TABLE_NAME                      Table, view or cluster name
COLUMN_NAME                     Column name
DATA_TYPE                       Datatype of the column
DATA_TYPE_MOD                   Datatype modifier of the column
DATA_TYPE_OWNER                 Owner of the datatype of the column
DATA_LENGTH                     Length of the column in bytes
DATA_PRECISION                  Length: decimal digits (NUMBER) or binary digits (FLOAT)