> For the complete documentation index, see [llms.txt](https://www.pranaypourkar.co.in/the-programmers-guide/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://www.pranaypourkar.co.in/the-programmers-guide/database/sql-databases/resources-and-references/oreilly-sql-cookbook-2nd-edition/5.-metadata-queries.md).
# 5. Metadata Queries
{% hint style="info" %}
Refer to the RDBMS’s documentation for the complete list of catalog or data dictionary tables/views for more details.
{% endhint %}
## 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)
```
---
# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.
## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:
```
GET https://www.pranaypourkar.co.in/the-programmers-guide/database/sql-databases/resources-and-references/oreilly-sql-cookbook-2nd-edition/5.-metadata-queries.md?ask=&goal=
```
`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.