1. USER_TAB_COLUMNS

I tabellen USER_TAB_COLUMNS listas alla tabellernas kolumner och information kring varje kolumn.

SQL> DESC user_tab_columns; Namn Null? Typ ----------------------------------------- -------- ---------------------------- TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) DATA_TYPE VARCHAR2(106) DATA_TYPE_MOD VARCHAR2(3) DATA_TYPE_OWNER VARCHAR2(30) DATA_LENGTH NOT NULL NUMBER DATA_PRECISION NUMBER DATA_SCALE NUMBER NULLABLE VARCHAR2(1) COLUMN_ID NUMBER DEFAULT_LENGTH NUMBER DATA_DEFAULT LONG NUM_DISTINCT NUMBER LOW_VALUE RAW(32) HIGH_VALUE RAW(32) DENSITY NUMBER NUM_NULLS NUMBER NUM_BUCKETS NUMBER LAST_ANALYZED DATE SAMPLE_SIZE NUMBER CHARACTER_SET_NAME VARCHAR2(44) CHAR_COL_DECL_LENGTH NUMBER GLOBAL_STATS VARCHAR2(3) USER_STATS VARCHAR2(3) AVG_COL_LEN NUMBER CHAR_LENGTH NUMBER CHAR_USED VARCHAR2(1) V80_FMT_IMAGE VARCHAR2(3) DATA_UPGRADED VARCHAR2(3)

2. Exempel

Jag brukar använda denna tabell främst för att hitta alla tabeller som innehåller en viss kolumn. Om jag t.ex. vill hitta alla tabeller där kolumnen ITEM_ORG_FK finns med skriver jag:

SQL> SELECT table_name, column_name FROM user_tab_columns WHERE column_name = 'ITEM_ORG_FK'; TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ EDWBG_ITEM_ITEMREV_LCV ITEM_ORG_FK ISCBG_BACKLOGS_BASE_FCV ITEM_ORG_FK ISCBG_BACKLOGS_FCV ITEM_ORG_FK ISCBG_BOOKINGS_FCV ITEM_ORG_FK EDW_ITEMS_ITEM_ORG_FKV ITEM_ORG_FK EDWBV_ITEM_ITEMREV_LCV ITEM_ORG_FK EDW_ITEM_ITEMREV_LCV ITEM_ORG_FK ISCBV_BACKLOGS_BASE_FCV ITEM_ORG_FK ISCBV_BACKLOGS_FCV ITEM_ORG_FK ISCBV_BOOKINGS_FCV ITEM_ORG_FK ISC_EDW_BACKLOGS_F_FCV ITEM_ORG_FK TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ ISC_EDW_BOOKINGS_F_FCV ITEM_ORG_FK OPI_EDW_OPIINV_DAILY_STAT_FCV ITEM_ORG_FK OPI_EDW_OPMINV_DAILY_STAT_FCV ITEM_ORG_FK OPI_EDW_COGS_FCV ITEM_ORG_FK OPI_EDW_OPMCOGS_FCV ITEM_ORG_FK 16 rader.