oracle外鍵sql語句

Ⅰ Oracle寫出相應的SQL語句 1. 系統管理員admin,密碼為adminpwd,登錄後創建用戶abc,密碼ab

系統管理員admin，密碼為adminpwd，登錄後創建用戶abc，密碼abcpwd，並創建data表空間作為該用戶的默認表空間，數據文件存儲在D盤project目錄。
create tablespace data datafile 'D/project'

create user abc identified by abcpwd default tablesapce data

Ⅱ 用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;