1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

View all table structure in schema

Discussion in 'SQL PL/SQL' started by professional, Dec 1, 2008.

  1. professional

    professional Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    Hello everyone, hope things are great here. Actually i was trying to do something and couldn't figure out any easy way. I want to view all table structure in a schema at once. Is there any simple way of doing this? other than writng a cursor and looping through all_tables and all_tab_columns?
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    I am not pretty sure about your requirement.

    whatever you do with the cursor for getting the table structure can be achieved through one straight SQL from the same table you meantioned.

    Code (Text):

    SELECT atc.TABLE_NAME,
           atc.COLUMN_NAME,
           atc.DATA_TYPE
    FROM  ALL_TAB_COLUMNS atc
     
    But this will give the table names from the the entire Database where the current schema exists. So it should be restricted to current user as .

    Code (Text):

    SELECT atc.TABLE_NAME,
           atc.COLUMN_NAME,
           atc.DATA_TYPE
    FROM  ALL_TAB_COLUMNS atc
    WHERE atc.OWNER = USER;
     
    Otherwise you can use USER_TAB_COLUMNS to achieve the same result.

    Code (Text):
    SELECT utc.TABLE_NAME,
           utc.COLUMN_NAME,
           utc.DATA_TYPE
    FROM  USER_TAB_COLUMNS utc
     
    Here is an example to show the EMP table structure.

    Code (Text):

    SQL> SELECT utc.TABLE_NAME ,
      2         utc.COLUMN_NAME ,
      3         utc.DATA_TYPE
      4  FROM  USER_TAB_COLUMNS utc
      5  WHERE utc.TABLE_NAME ='EMP';

    TABLE_NAME           COLUMN_NAME          DATA_TYPE
    -------------------- -------------------- --------------------
    EMP                  EMPNO                NUMBER
    EMP                  ENAME                VARCHAR2
    EMP                  JOB                  VARCHAR2
    EMP                  MGR                  NUMBER
    EMP                  HIREDATE             DATE
    EMP                  SAL                  NUMBER
    EMP                  COMM                 NUMBER
    EMP                  DEPTNO               NUMBER

    8 rows selected.

    SQL>
     
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Or this can be spooled as formatted report from SQL*PLUS as below.
    Example is given only for standard Data types ( VARCHAR2 , NUMBER and DATE)

    Code (Text):

    SQL> break on TABLE_NAME
    SQL> SELECT utc.TABLE_NAME ,
      2         utc.COLUMN_NAME ,
      3         utc.DATA_TYPE  ,
      4        CASE DATA_TYPE
      5        WHEN 'VARCHAR2' THEN '('||DATA_LENGTH||')'
      6        WHEN 'NUMBER' THEN  CASE
      7                          WHEN DATA_PRECISION IS NOT NULL
      8                          THEN  '('||DATA_PRECISION||CASE
      9                                                     WHEN DATA_SCALE <>0
     10                                                     THEN ','||DATA_SCALE END
     11                          END||CASE WHEN DATA_PRECISION IS NOT NULL
     12                               THEN ')' END
     13        END DATA_LENGTH
     14  FROM  USER_TAB_COLUMNS utc
     15  WHERE utc.TABLE_NAME IN ('EMP','DEPT','TEST');

    TABLE_NAME           COLUMN_NAME          DATA_TYPE            DATA_LENGTH
    -------------------- -------------------- -------------------- ---------------
    DEPT                 DEPTNO               NUMBER               (2)
                         DNAME                VARCHAR2             (14)
                         LOC                  VARCHAR2             (13)
    EMP                  EMPNO                NUMBER               (4)
                         ENAME                VARCHAR2             (10)
                         JOB                  VARCHAR2             (9)
                         MGR                  NUMBER               (4)
                         HIREDATE             DATE
                         SAL                  NUMBER               (7,2)
                         COMM                 NUMBER               (7,2)
                         DEPTNO               NUMBER               (2)
    TEST                 ACCOUNT              NUMBER
                         BALANCE              NUMBER

    13 rows selected.

    SQL>
     
     
  4. professional

    professional Active Member

    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    80
    hi rajavu, thanks bro you solved the problem. Basically I wanted to see the table structures for all tables in a given schema. I used your last query and expanded it for many data types and it works just fine.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    By the way ,

    There is One more way for doing the same by using the package dbms_metadata. dbms_metadata.get_ddl wil do the trick.

    Its general syntax is :

    Example:

    Code (Text):

    SQL> SELECTDBMS_METADATA.GET_DDL
      2         ( 'TABLE', 'EMP','SCOTT') DDL
      3    FROM DUAL;


      CREATE TABLE "SCOTT"."EMP"
       (    "EMPNO" NUMBER(4,0),
            "ENAME" VARCHAR2(10),
            "JOB" VARCHAR2(9),
            "MGR" NUMBER(4,0),
            "HIREDATE" DATE,
            "SAL" NUMBER(7,2),
            "COMM" NUMBER(7,2),
            "DEPTNO" NUMBER(2,0),
             CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
      USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "USERS"  ENABLE,
             CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
              REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
      TABLESPACE "USERS"



    SQL>
     
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    By the way , I also came across almost the same issue. The following link was more useful , though it resembles previous solution.
     
  7. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    To be precise , you can control the Tablespace , storage , constraints etc using the DBMS_METADATA.SET_TRANSFORM_PARAM . This is used for controlling the sesion attributes for the DBMS_METADATA package. This is the same thing used in the Third part tools.

    See the example below

    Code (Text):

    SQL> EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', TRUE);

    PL/SQL procedure successfully completed.

    SQL> EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', TRUE );

    PL/SQL procedure successfully completed.

    SQL> EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', FALSE);

    PL/SQL procedure successfully completed.

    SQL> EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'STORAGE', FALSE);

    PL/SQL procedure successfully completed.

    SQL> EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'TABLESPACE', FALSE);

    PL/SQL procedure successfully completed.

    SQL> EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS','FALSE');

    PL/SQL procedure successfully completed.

    SQL> EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'REF_CONSTRAINTS','FALSE');

    PL/SQL procedure successfully completed.

    SQL> EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', TRUE);

    PL/SQL procedure successfully completed.

    SQL> EXEC dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SIZE_BYTE_KEYWORD', TRUE );

    PL/SQL procedure successfully completed.

    SQL> set long 2000
    SQL> set linesize 200
    SQL> set pagesize 0
    SQL> SELECT DBMS_METADATA.GET_DDL( 'TABLE', 'EMP','SCOTT') DDL FROM DUAL;

      CREATE TABLE "SCOTT"."EMP"
       (    "EMPNO" NUMBER(4,0),
            "ENAME" VARCHAR2(10 BYTE),
            "JOB" VARCHAR2(9 BYTE),
            "MGR" NUMBER(4,0),
            "HIREDATE" DATE,
            "SAL" NUMBER(7,2),
            "COMM" NUMBER(7,2),
            "DEPTNO" NUMBER(2,0)
       ) ;



    SQL>