WITH TMP_PK AS (--根据PK主键 把某用户名下的某些表导出
SELECT A.OWNER
,A.TABLE_NAME
,A.COLUMN_NAME
,COUNT(1) PK_NUM --计算该表是否已有主键
FROM ALL_CONS_COLUMNS A
JOIN ALL_CONSTRAINTS B
ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
AND A.OWNER = B.OWNER
AND B.OWNER = 'SCOTT'
WHERE B.CONSTRAINT_TYPE = 'P' -- CONSTRAINT_TYPE = 'P' 选出约束类型是PRIMARY KEY
AND A.OWNER = 'SCOTT'
GROUP BY A.OWNER
,A.TABLE_NAME
,A.COLUMN_NAME
)
, TMP_UNI AS (
SELECT A.OWNER --根据UNIQUE唯一索引,把某用户名下的某些表导出
,A.TABLE_NAME
,B.COLUMN_NAME
FROM ALL_INDEXES A
JOIN ALL_IND_COLUMNS B
ON A.INDEX_NAME = B.INDEX_NAME
AND B.TABLE_OWNER = 'SCOTT'
WHERE A.OWNER = 'SCOTT'
AND A.UNIQUENESS = 'UNIQUE' --把唯一索引选出来
)
SELECT A.OWNER
,A.TABLE_NAME
,A.COLUMN_ID
,A.COLUMN_NAME
,(CASE
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_PRECISION IS NULL AND A.DATA_SCALE = 0
THEN 'INT'
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_PRECISION IS NULL AND A.DATA_SCALE IS NULL
THEN 'NUMBER'
WHEN A.DATA_TYPE = 'NUMBER' AND A.DATA_PRECISION IS NOT NULL
THEN 'NUMBER(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')'
WHEN A.DATA_TYPE = 'TIMESTAMP(6)'
THEN 'TIMESTAMP'
WHEN A.DATA_TYPE = 'VARCHAR2'
THEN 'VARCHAR2(' || DATA_LENGTH || ')'
WHEN A.DATA_TYPE = 'CHAR'
THEN 'CHAR(' || DATA_LENGTH || ')'
WHEN A.DATA_TYPE IN ('CLOB', 'BLOB', 'DATE')
THEN A.DATA_TYPE
ELSE 'VARCHAR2(4000)'
END )AS DATA_TYPE
,B.COMMENTS
, CASE WHEN PK1.PK_NUM > 0 --若该表有主键,直接取
THEN (CASE WHEN PK.COLUMN_NAME IS NOT NULL THEN '是' ELSE '否' END)
ELSE (CASE WHEN UNI.COLUMN_NAME IS NOT NULL THEN '是' ELSE '否' END) --无主键,有唯一索引则取唯一索引做主键
END AS V_PRIMARY1
,DECODE(A.NULLABLE, 'Y', '否' , '是')AS NULLABLE --是否可为空
FROM ALL_TAB_COLS A
JOIN ALL_COL_COMMENTS B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.COLUMN_NAME = B.COLUMN_NAME
AND A.OWNER = B.OWNER
LEFT JOIN TMP_PK PK
ON A.OWNER = PK.OWNER
AND A.TABLE_NAME = PK.TABLE_NAME
AND A.COLUMN_NAME = PK.COLUMN_NAME
LEFT JOIN TMP_PK PK1
ON A.OWNER = PK1.OWNER
AND A.TABLE_NAME = PK1.TABLE_NAME
LEFT JOIN TMP_UNI UNI
ON A.OWNER = UNI.OWNER
AND A.TABLE_NAME = UNI.TABLE_NAME
AND A.COLUMN_NAME = UNI.COLUMN_NAME
WHERE A.OWNER = 'SCOTT'
AND A.TABLE_NAME IN ('表名')
ORDER BY A.TABLE_NAME
,A.COLUMN_ID
Oracle获取表结构(包括主键)
该SQL脚本查询了SCOTT用户下的特定表,包括其所有者、表名、列名、数据类型、是否为主键/唯一索引以及可空性。脚本通过JOIN操作结合主键和唯一索引来确定表结构特征。
摘要由CSDN通过智能技术生成