最全的表字段信息查询

SELECT 
DT.OWNER 用户名,
DT.TABLE_NAME 表名,
DT.TABLE_COMMENTS 表注释,
DT.TABLESPACE_NAME 所属表空间,
DT.TABLE_PK 表主键,
DTC.COLUMN_NAME 字段名,
CASE 
WHEN DTC.DATA_PRECISION IS NOT NULL AND DTC.DATA_SCALE IS NOT NULL THEN DTC.DATA_TYPE||'('||DTC.DATA_PRECISION||','||DTC.DATA_SCALE||')'
WHEN DTC.DATA_TYPE LIKE '%TIME%' THEN DTC.DATA_TYPE||'('||DTC.DATA_SCALE||')'
WHEN DATA_TYPE LIKE '%INT%' OR DATA_TYPE IN ('BIT','BLOB','CLOB','TEXT','DATE','IMAGE','LONGVARCHAR') THEN DATA_TYPE
ELSE DTC.DATA_TYPE||'('||DTC.DATA_LENGTH||')'
END 字段类型,
CC.COMMENTS 字段注释,
CASE WHEN DIC.COLUMN_NAME IS NULL THEN '否' ELSE '是' END 是否有索引,
CASE WHEN DTC.NULLABLE='N' THEN '否' ELSE '是' END 是否可为空,
DTC.DATA_DEFAULT 默认值
FROM(
SELECT 
DT.OWNER OWNER,
DT.TABLE_NAME TABLE_NAME,
TC.COMMENTS TABLE_COMMENTS,
DT.TABLESPACE_NAME TABLESPACE_NAME,
LISTAGG(DCC.COLUMN_NAME,',') WITHIN GROUP (ORDER BY POSITION) TABLE_PK
FROM DBA_TABLES DT
LEFT JOIN DBA_TAB_COMMENTS TC ON DT.OWNER=TC.OWNER AND DT.TABLE_NAME=TC.TABLE_NAME
LEFT JOIN DBA_CONSTRAINTS DC ON DT.OWNER=DC.OWNER AND DT.TABLE_NAME=DC.TABLE_NAME AND DC.CONSTRAINT_TYPE='P'
LEFT JOIN DBA_CONS_COLUMNS DCC ON DC.CONSTRAINT_NAME=DCC.CONSTRAINT_NAME
WHERE DT.OWNER='用户名' 
GROUP BY DT.OWNER,DT.TABLE_NAME,TC.COMMENTS,DT.TABLESPACE_NAME
)DT
LEFT JOIN DBA_TAB_COLUMNS DTC ON DT.OWNER=DTC.OWNER AND DT.TABLE_NAME=DTC.TABLE_NAME
LEFT JOIN DBA_COL_COMMENTS CC ON DTC.OWNER=CC.OWNER AND DTC.TABLE_NAME=CC.TABLE_NAME AND DTC.COLUMN_NAME=CC.COLUMN_NAME
LEFT JOIN (SELECT DISTINCT INDEX_OWNER,TABLE_OWNER,TABLE_NAME,COLUMN_NAME FROM DBA_IND_COLUMNS)DIC 
ON DTC.OWNER=DIC.TABLE_OWNER AND DTC.TABLE_NAME=DIC.TABLE_NAME AND DTC.COLUMN_NAME=DIC.COLUMN_NAME
ORDER BY DT.OWNER,DT.TABLE_NAME;

				

					
				
				

					
				
				

					
				
				

					
				
			

另：

查询表中某一列的最大值和最小值

SELECT
    USR.NAME AS OWNER_NAME,
    SCH.NAME AS SCHEMA_NAME,
    TAB.NAME AS TABLE_NAME,
    COL.NAME AS COLUMN_NAME,
    COL.TYPE$ AS COLUMN_TYPE,
    COL.COLID AS COLUMN_ID,
    CASE COL.TYPE$
        WHEN 'NUMBER' THEN
            ROUND(CAST(SF_BIN_GET_DOUBLE(S.V_MIN, 0) AS NUMBER), COL.SCALE)
        WHEN 'INT' THEN
            SF_BIN_GET_INT(S.V_MIN, 0)
        WHEN 'INTEGER' THEN
            SF_BIN_GET_INT(S.V_MIN, 0)
        WHEN 'BIGINT' THEN
            SF_BIN_GET_BIGINT(S.V_MIN, 0)
        WHEN 'SMALLINT' THEN
            SF_BIN_GET_SMALLINT(S.V_MIN, 0)
        WHEN 'TINYINT' THEN
            SF_BIN_GET_TINYINT(S.V_MIN, 0)
        WHEN 'CHAR' THEN
            BINTOCHAR2(S.V_MIN)
        WHEN 'NCHAR' THEN
            BINTOCHAR2(S.V_MIN)
        WHEN 'NVARCHAR' THEN
            BINTOCHAR2(S.V_MIN)
        WHEN 'NVARCHAR2' THEN
            BINTOCHAR2(S.V_MIN)
        WHEN 'VARCHAR2' THEN
            BINTOCHAR2(S.V_MIN)
        WHEN 'VARCHAR' THEN
            BINTOCHAR2(S.V_MIN)
        WHEN 'VARBINARY' THEN
            NVL2(S.V_MIN, '0X' || RAWTOHEX(SUBSTRBLB(S.V_MIN, 5, SF_BIN_GET_INT(S.V_MIN, 0))), NULL)
        WHEN 'DATE' THEN
            TO_CHAR(BINTODATE(S.V_MIN), 'YYYY-MM-DD')
        WHEN 'DATETIME' THEN
            TO_CHAR(BINTODATE(S.V_MIN), 'YYYY-MM-DD HH24:MI:SS')
        WHEN 'DATETIME WITH TIME ZONE' THEN
            TO_CHAR(BINTODATE(S.V_MIN), 'YYYY-MM-DD HH24:MI:SS TZH:TZM')
        WHEN 'TIMESTAMP' THEN
            TO_CHAR(BINTODATE(S.V_MIN), 'YYYY-MM-DD HH24:MI:SS.FF6')
        ELSE
            RAWTOHEX(S.V_MIN)
    END AS VALUE_MIN,
    CASE COL.TYPE$
        WHEN 'NUMBER' THEN
            ROUND(CAST(SF_BIN_GET_DOUBLE(S.V_MAX, 0) AS NUMBER), COL.SCALE)
        WHEN 'INT' THEN
            SF_BIN_GET_INT(S.V_MAX, 0)
        WHEN 'INTEGER' THEN
            SF_BIN_GET_INT(S.V_MAX, 0)
        WHEN 'BIGINT' THEN
            SF_BIN_GET_BIGINT(S.V_MAX, 0)
        WHEN 'SMALLINT' THEN
            SF_BIN_GET_SMALLINT(S.V_MAX, 0)
        WHEN 'TINYINT' THEN
            SF_BIN_GET_TINYINT(S.V_MAX, 0)
        WHEN 'CHAR' THEN
            BINTOCHAR2(S.V_MAX)
        WHEN 'NCHAR' THEN
            BINTOCHAR2(S.V_MAX)
        WHEN 'NVARCHAR' THEN
            BINTOCHAR2(S.V_MAX)
        WHEN 'NVARCHAR2' THEN
            BINTOCHAR2(S.V_MAX)
        WHEN 'VARCHAR2' THEN
            BINTOCHAR2(S.V_MAX)
        WHEN 'VARCHAR' THEN
            BINTOCHAR2(S.V_MAX)
        WHEN 'VARBINARY' THEN
            NVL2(S.V_MAX, '0X' || RAWTOHEX(SUBSTRBLB(S.V_MAX, 5, SF_BIN_GET_INT(S.V_MAX, 0))), NULL)
        WHEN 'DATE' THEN
            TO_CHAR(BINTODATE(S.V_MAX), 'YYYY-MM-DD')
        WHEN 'DATETIME' THEN
            TO_CHAR(BINTODATE(S.V_MAX), 'YYYY-MM-DD HH24:MI:SS')
        WHEN 'DATETIME WITH TIME ZONE' THEN
            TO_CHAR(BINTODATE(S.V_MAX), 'YYYY-MM-DD HH24:MI:SS TZH:TZM')
        WHEN 'TIMESTAMP' THEN
            TO_CHAR(BINTODATE(S.V_MAX), 'YYYY-MM-DD HH24:MI:SS.FF6')
        ELSE
            RAWTOHEX(S.V_MAX)
    END AS VALUE_MAX,
    S.T_TOTAL,
    S.N_SAMPLE,
    S.N_DISTINCT,
    S.N_NULL,
    S.COL_AVG_LEN,
    S.LAST_GATHERED,
    S.DATA
FROM
    SYSSTATS S,
    SYSOBJECTS TAB,
    SYSCOLUMNS COL,
    SYSOBJECTS SCH,
    SYSOBJECTS USR
WHERE
    S.ID = TAB.ID
    AND S.T_FLAG = 'C'
    AND COL.ID = S.ID
    AND COL.COLID = S.COLID
    AND SCH.ID = TAB.SCHID
    AND SCH.TYPE$ = 'SCH'
    AND TAB.TYPE$ = 'SCHOBJ'
    AND TAB.SUBTYPE$ IN ('UTAB', 'STAB')
    AND USR.TYPE$ = 'UR'
    AND USR.ID = SCH.PID
    AND SCH.NAME = 'SCHNAME'
    AND TAB.NAME = 'TABNAME'
    AND COL.NAME = 'COLNAME'
ORDER BY
    1,2,3,6;