Oracle - Exploring

oracle

How can we obtain information about a table?

select owner, table_name from dba_tables where table_name='DUAL';

How can we display all the tables in a schema?

SELECT * FROM cat;

How can we obtain the list of columns for a given table in a given schema?

SELECT COLUMN_NAME FROM ALL_TAB_COLUMNS where TABLE_NAME='ENTITIES' AND OWNER='QXPLORE';

How can we determine the version?

select * from v$version;

How can we view comments?

To view the comments on a particular table or column:

SELECT * FROM ALL_TAB_COMMENTS;
SELECT * FROM ALL_COL_COMMENTS;
SELECT * FROM USER_TAB_COMMENTS;
SELECT * FROM DBA_TAB_COMMENTS;
SELECT * FROM USER_COL_COMMENTS;
SELECT * FROM DBA_COL_COMMENTS;

How can we obtain information on constraints?

Query all_constraints (or user_constraints) with table name:

SELECT *
FROM   user_constraints
WHERE  table_name = ?
SELECT DISTINCT table_name
FROM all_indexes
WHERE index_name = 'CONSTRAINT_NAME';
SELECT DISTINCT table_name
FROM all_indexes
WHERE index_name = 'SYS_C002459';

select table_name from all_indexes where index_name='<index-name>';
select * from all_ind_columns where index_name='<index-name>';
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License