Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2003
    Posts
    2

    Unanswered: List of tables and their fields (SQL)

    Is there a simpler command to output a list of table_names, column_names, column_types, column_length, column_null and index_name other that the one I'm currently using;

    SET LINESIZE 255
    SPOOL c:\temp\ATOMtables.doc
    COLUMN TABLE_NAME FORMAT A30
    COLUMN COLUMN_NAME FORMAT A30
    COLUMN COLUMN_TYPE FORMAT A15
    COLUMN COLUMN_LENGTH FORMAT A50
    COLUMN COLUMN_NULL FORMAT A10
    COLUMN INDEX_NAME FORMAT A30
    SELECT
    DBA_TAB.TABLE_NAME TABLE_NAME,
    DBA_TAB_COL.COLUMN_NAME COLUMN_NAME,
    DBA_TAB_COL.DATA_TYPE COLUMN_TYPE,
    DBA_TAB_COL.DATA_LENGTH COLUMN_LENGTH,
    DBA_TAB_COL.NULLABLE COLUMN_NULL,
    DBA_INDX_COL.INDEX_NAME INDEX_NAME
    FROM DBA_DATA_FILES DBA_DF,
    DBA_TABLES DBA_TAB,
    DBA_TAB_COLUMNS DBA_TAB_COL,
    DBA_IND_COLUMNS DBA_INDX_COL,
    DBA_COL_COMMENTS DBA_COL_COM,
    DBA_SEGMENTS DBA_SEG
    WHERE DBA_TAB_COL.OWNER = 'RATO'
    AND DBA_TAB.OWNER = 'RATO'
    AND DBA_SEG.OWNER = 'RATO'
    AND DBA_TAB.TABLESPACE_NAME = DBA_DF.TABLESPACE_NAME
    AND DBA_TAB.TABLE_NAME = DBA_TAB_COL.TABLE_NAME
    AND DBA_TAB_COL.TABLE_NAME = DBA_INDX_COL.TABLE_NAME(+)
    AND DBA_TAB_COL.COLUMN_NAME = DBA_INDX_COL.COLUMN_NAME(+)
    AND DBA_COL_COM.TABLE_NAME(+) = DBA_TAB_COL.TABLE_NAME
    AND DBA_COL_COM.COLUMN_NAME(+) = DBA_TAB_COL.COLUMN_NAME
    AND DBA_SEG.SEGMENT_NAME = DBA_TAB_COL.TABLE_NAME
    AND DBA_SEG.SEGMENT_TYPE = 'TABLE'
    AND SUBSTR(DBA_TAB.TABLE_NAME,1,3) <> 'DS_'
    ORDER BY
    DBA_TAB_COL.TABLE_NAME,
    DBA_TAB_COL.COLUMN_NAME
    /

    I thought there might be a easier solution.

    Also, I only want each table_name returned once. I've tried using DISTINCT but keep getting SQL errors returned

    Thanks

    Neil

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can use the SQL Plus command "BREAK ON TABLE_NAME" to get the table names to print only once.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •