用SQL输出Oracle表结构（包括主键、外键）

with M as

(SELECT t.table_name,

t.column_NAME,

t.DATA_TYPE || '(' ||t.DATA_LENGTH || ')' datatype,

t1.COMMENTS

FROM User_Tab_Columnst,

User_Col_Comments t1,

USER_TABLES T2

WHERE t.table_name =t1.table_name

AND t.column_name = t1.column_name

AND T.TABLE_NAME = T2.TABLE_NAME

ORDER BY T1.table_name),

P as (select P.table_name,

C.column_name

from user_constraints P,

user_cons_columns C ,

USER_TABLES T

whereP.constraint_type='P'

andP.table_name = T.TABLE_NAME

andP.constraint_name=C.constraint_name),

R as

(Select a.Owner fowner,

a.Table_Name ftable,

c.Column_Name fcolumn,

b.Owner mowner,

b.Table_Name mtable,

d.Column_Name mcolumn,

c.Constraint_Name fname,

d.Constraint_Name mname

From User_Constraints a,

user_Constraints b,

user_Cons_Columns c,

user_Cons_Columns d

Where a.r_Constraint_Name =b.Constraint_Name

And a.Constraint_Type = 'R'

And b.Constraint_Type = 'P'

And a.r_Owner = b.Owner

And a.Constraint_Name = c.Constraint_Name

And b.Constraint_Name = d.Constraint_Name

And a.Owner = c.Owner

And a.Table_Name = c.Table_Name

And b.Owner = d.Owner

And b.Table_Name = d.Table_Name)

select M.table_name,

M.colUMN_NAME,

M.datatype,

M.comments,

(select count(*)

from P

where P.table_name=M.table_name

and P.column_name=M.column_name

) as是否主键,--0表示是非主键，非0表示是主键

(select mtable

from R

where R.ftable=M.table_name

and R.fcolumn=M.column_name

and rownum=1

) as外键表

from M;