如何获得Oracle的表结构？

Question

我想获取数据库的表结构信息，分别要取得表名，注释，是否为空，是否为主健等信息，示例(SQL Server)/*
Author:Conis
CreateDate:16:21 2007-08-09
Description:
获取指定表的所有字段属性
Re:
　1.如果你不精通SQL代码，请不要随便修改，以免程序不能正常运行。
` 2.字段名称不能修改，否则会导致程序错误
3.如果你一定要修改，建议在修改之前做好备份
=======================================================================
|                     CopyRight(C)Conis YI                            |
|                    URL:http://www.conis.cn                          |
|                   E-Mail:[email protected]                         |
=======================================================================
*/DECLARE @Version VARCHAR(100)
DECLARE @SysTable VARCHAR(50)
DECLARE @sql NVARCHAR(2000)
DECLARE @SmallID VARCHAR(10)
DECLARE @MajorID VARCHAR(10)SET @Version = @@VERSION
IF CHARINDEX('9.00', @Version) = 0 --2005
BEGIN
SET @SysTable = 'sysproperties'
SET @SmallID = 'smallid'
SET @MajorID = 'id'
END
ELSE --2000
BEGIN
SET @SysTable = 'sys.extended_properties'
SET @SmallID = 'minor_id'
SET @MajorID = 'major_id'
ENDSET @sql = 
'SELECT 
 col.name AS ''FieldName'',
 (CASE WHEN (SELECT COUNT(*)
 FROM sysobjects
 WHERE (name in
           (SELECT name
          FROM sysindexes
          WHERE (id = col.id) AND (indid in
                    (SELECT indid
                   FROM sysindexkeys
                   WHERE (id = col.id) AND (colid in
                             (SELECT colid
                            FROM syscolumns
                            WHERE (id = col.id) AND (name = col.name))))))) AND
        (xtype = ''PK''))>0 THEN 1 ELSE 0 END) AS ''IsKey'',
typ.name AS ''Type'',
col.length AS ''Length'',
ext.[value] AS ''Description'',
col.isnullable AS ''AllowNull'',
com.text AS ''DefaultValue'',
col.colstat AS ''IsOutPut'',
obj.Name AS ''TableName''FROM syscolumns col 
 LEFT JOIN systypes typ on col.xtype = typ.xusertype
 INNER JOIN sysobjects obj on col.id = obj.id
 LEFT JOIN syscomments com on col.cdefault = com.id
 LEFT JOIN [PROPERTIES] ext on col.id = ext.[MAJORID] AND col.colid = ext.[SMALLID]
WHERE 
 obj.name in ({0}) AND (obj.xtype = ''U'' OR obj.xtype = ''V'')
ORDER BY obj.name'SET @sql = REPLACE(@sql, '[PROPERTIES]', @SysTable)
SET @sql = REPLACE(@sql, '[SMALLID]', @SmallID)
SET @sql = REPLACE(@sql, '[MAJORID]', @MajorID)
EXEC sp_executesql @sql/*
Author:Conis
CreateDate:16:50 2007-08-09
Description:
获取所有表的属性
Re:
　1.如果你不精通SQL代码，请不要随便修改，以免程序不能正常运行。
` 2.字段名称不能修改，否则会导致程序错误
3.如果你一定要修改，建议在修改之前做好备份
=======================================================================
|                     CopyRight(C)Conis YI                            |
|                    URL:http://www.conis.cn                          |
|                   E-Mail:[email protected]                         |
=======================================================================
*/
DECLARE @Version VARCHAR(100)
DECLARE @SysTable VARCHAR(50)
DECLARE @sql NVARCHAR(1000)
DECLARE @SmallID VARCHAR(10)SET @Version = @@VERSION
IF CHARINDEX('9.00', @Version) = 0 --2005
BEGIN
SET @SysTable = 'sysproperties'
SET @SmallID = 'smallid'
END
ELSE --2000
BEGIN
SET @SysTable = 'sys.extended_properties'
SET @SmallID = 'minor_id'
ENDSET @sql = '
SELECT obj.name AS ''Name'', 
CASE --如果是表，则查询表的备注信息。
WHEN obj.xtype = ''U'' THEN
(SELECT TOP 1 ext.value FROM [PROPERTIES] ext WHERE 
ext.[SMALLID] = obj.id and ext.[SMALLID] = 0
)
ELSE ''''
END AS ''Description'',
CASE obj.xtype
WHEN ''U'' THEN ''Table''
WHEN ''P'' THEN ''Procedure''
WHEN ''V'' THEN ''View''
END AS ''Type''
 FROM sysobjects obj 
WHERE (obj.xtype = ''U'' OR obj.xtype = ''V'')
ORDER BY obj.xtype'
SET @sql = REPLACE(@sql, '[PROPERTIES]', @SysTable)
SET @sql = REPLACE(@sql, '[SMALLID]', @SmallID)
EXEC sp_executesql @sql

Answer 1

Oracle的话，去
USER_TABLE
USER_TAB_COMMENTS
USER_CONSTRAINTS
USER_TAB_COLUMNS
USER_COL_COMMENTS
这些系统视图里面可以找到.

Answer 2

select   DBMS_METADATA.GET_DDL(对象类型,对象名称)   from   dual; 对象类型： 
CLUSTER 
TABLE   表 
INDEX 
REF_CONSTRAINT 
CONSTRAINT 
VIEW 
TYPE 
FUNCTION 
PROCEDURE 
PACKAGE 
SEQUENCE 
TRIGGER 
SYNONYM 
DB_LINK 如： 
select   DBMS_METADATA.GET_DDL('TABLE','MYTABLE')   from   dual;如果要同时获取多个表的结构： 
select       DBMS_METADATA.GET_DDL('TABLE',table_name)     from       USER_TABLES;

Answer 3

或者直接取xml信息
select       DBMS_METADATA.GET_XML('TABLE','MYTABLE')       from       dual; 

Answer 4

怎么判断哪个是主键？
差不多写出来，但不知道如何获取主键
SELECT B.Table_Name AS TableName, B.COLUMN_Name AS FieldName, Comments AS Description,
       B.DATA_TYPE AS Type, B.DATA_LENGTH AS Length, B.DATA_DEFAULT AS DefaultValue, 0 AS IsOutPut,
       DECODE(B.NULLABLE, 'Y', '1', '0') AS AllowNull, B.
 FROM USER_COL_COMMENTS A LEFT JOIN USER_TAB_COLUMNS B
 ON A.table_name = B.TABLE_NAME AND A.column_name = B.COLUMN_NAME

Answer 5

从all_constraints里面找，如果constraint_type='P'则说明他是主键。SQL> select constraint_name,owner,constraint_type from all_constraints where tab
le_name='SYS_USER' and constraint_type='P';CONSTRAINT_NAME                OWNER                          C
------------------------------ ------------------------------ -
PK_SYS_USER                    TEXTLIU                        P

Answer 6

没有办法得到主键的字段名，PK_SYS_USER不是这段名，是约束名称