Databases Reference
In-Depth Information
ALL_TAB_COLUMNS
The ALL_TAB_COLUMNS view contains information about the columns in all
tables accessible to the user. If the user HR wanted to find out the columns and
datatypes in the COUNTRIES table, the query would be written as follows:
select column_name, data_type from all_tab_columns
where table_name = 'COUNTRIES';
COLUMN_NAME DATA_TYPE
------------------------- ------------
COUNTRY_ID CHAR
COUNTRY_NAME VARCHAR2
REGION_ID NUMBER
3 rows selected.
ALL_INDEXES
The ALL_INDEXES view contains information about the indexes accessible
to the user. If the HR user wanted to find out the indexes that were created
against the COUNTRIES table and whether the indexes were unique, the query
would look like this:
select table_name, index_name, uniqueness from all_indexes
where table_name = 'COUNTRIES';
TABLE_NAME INDEX_NAME UNIQUENESS
------------------------ -------------------- ---------
COUNTRIES COUNTRY_C_ID_PK UNIQUE
COUNTRIES COUNTRIES_IE1 NONUNIQUE
2 rows selected.
The COUNTRIES table has two indexes, one of which is a unique index.
ALL_IND_COLUMNS
The ALL_IND_COLUMNS view contains information about the columns indexed
by an index on a table. Following the previous example, the HR user can use the
INDEX_NAME to help identify the indexed column or columns on the table:
select table_name, column_name from all_ind_columns
where index_name = 'COUNTRY_C_ID_PK';
