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)