Oracle查表信息

记录Oracle查询语句
查询表信息:

--查询表信息
WITH COL_INF AS (
    SELECT
        T1.TABLE_NAME,
        T2.TABLE_NAME R_TABLE_NAME,
        A1.COLUMN_NAME,
        A2.COLUMN_NAME R_COLUMN_NAME
    FROM USER_CONSTRAINTS T1
             JOIN USER_CONSTRAINTS T2 ON T1.R_CONSTRAINT_NAME = T2.CONSTRAINT_NAME
             JOIN USER_CONS_COLUMNS A1 ON T1.CONSTRAINT_NAME = A1.CONSTRAINT_NAME
             JOIN USER_CONS_COLUMNS A2 ON T1.R_CONSTRAINT_NAME = A2.CONSTRAINT_NAME
    WHERE T1.OWNER='WWW' AND T1.CONSTRAINT_TYPE='R'
)
SELECT --ROW_NUMBER() over (ORDER BY A.COLUMN_ID) AS "序号",
--A.TABLE_NAME AS "表名",
       A.COLUMN_NAME AS "字段名",
       B.COMMENTS AS "字段说明",
       C.R_TABLE_NAME AS "关联表",
       A.DATA_TYPE||NVL2(NULLIF(NVL(A.DATA_PRECISION,A.CHAR_LENGTH),0),'('||NVL(A.DATA_PRECISION,A.CHAR_LENGTH)||')','') AS "数据类型",
       NVL2(NULLIF(A.NULLABLE,'N'),'是','否') AS "可空"
FROM USER_TAB_COLUMNS A
         JOIN USER_COL_COMMENTS B
              ON A.TABLE_NAME=B.TABLE_NAME AND A.COLUMN_NAME=B.COLUMN_NAME
         LEFT JOIN COL_INF C ON A.TABLE_NAME=C.TABLE_NAME AND A.COLUMN_NAME=C.COLUMN_NAME
WHERE A.TABLE_NAME=UPPER('BARCODE_RULES')
ORDER BY A.TABLE_NAME,A.COLUMN_ID
;

顺便标记一下 lead 和 lag 函数 切割时间段超好用