Ask the Expert

Extracting ddl for a view

How do I extract ddl for a view? I want to do it in SQL, not through another Oracle tool.

    Requires Free Membership to View

Use either the system view called user_views, all_views or dba_views and retrieve the TEXT column.

SQL> desc user_views
 Name                     Null?    Type
 ------------------------ -------- ---------------
 VIEW_NAME                NOT NULL VARCHAR2(30)
 TEXT_LENGTH                       NUMBER
 TEXT                              LONG
 TYPE_TEXT_LENGTH                  NUMBER
 TYPE_TEXT                         VARCHAR2(4000)
 OID_TEXT_LENGTH                   NUMBER
 OID_TEXT                          VARCHAR2(4000)
 VIEW_TYPE_OWNER                   VARCHAR2(30)
 VIEW_TYPE                         VARCHAR2(30)

SQL> set long 100000
SQL> select text 
  2    from all_views
  3   where view_name = 'ALL_COL_COMMENTS' ;  <--- your view name goes here

TEXT
----------------------------------------------------------
select u.name, o.name, c.name, co.comment$
from sys.obj$ o, sys.col$ c, sys.user$ u, sys.com$ co
where o.owner# = u.user#
  and o.type# in (2, 4, 5)
  and o.obj# = c.obj#
  and c.obj# = co.obj#(+)
  and c.intcol# = co.col#(+)
  and bitand(c.property, 32) = 0 /* not hidden column */
  and (o.owner# = userenv('SCHEMAID')
       or o.obj# in
         (select obj#
          from sys.objauth$
          where grantee# in ( select kzsrorol
                              from x$kzsro
                            )
          )
       or
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */))
      )
      
SQL>      

For More Information


This was first published in April 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: