摘要:获取表字段、索引、主键值的SQL
获取表字段、索引、主键值的SQL
1、表字段
SELECT
C.OWNER, C.TABLE_NAME, C.COLUMN_ID, C.COLUMN_NAME,
DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_DEFAULT,
NULLABLE, COMMENTS
FROM ALL_TAB_COLUMNS C JOIN ALL_TABLES T ON C.OWNER = T.OWNER AND C.TABLE_NAME = T.TABLE_NAME
LEFT JOIN ALL_COL_COMMENTS R ON C.OWNER = R.Owner AND C.TABLE_NAME = R.TABLE_NAME AND
C.COLUMN_NAME = R.COLUMN_NAME
WHERE C.OWNER = 'SYS'
ORDER BY C.TABLE_NAME, C.COLUMN_ID
2、索引
SELECT
I.TABLE_OWNER, I.TABLE_NAME, I.INDEX_NAME, I.INDEX_TYPE,
I.UNIQUENESS, C.COLUMN_POSITION, C.COLUMN_NAME, C.DESCEND
FROM ALL_INDEXES I JOIN ALL_IND_COLUMNS C
ON I.TABLE_OWNER = C.TABLE_OWNER AND I.INDEX_NAME = C.INDEX_NAME
WHERE C.TABLE_OWNER = 'SYS'
ORDER BY I.TABLE_NAME, I.INDEX_NAME, COLUMN_POSITION
3、主鍵值
SELECT
C.OWNER, C.TABLE_NAME, D.POSITION, D.COLUMN_NAME
FROM
ALL_CONSTRAINTS C JOIN ALL_CONS_COLUMNS D
ON C.OWNER = D.OWNER AND C.CONSTRAINT_NAME = D.CONSTRAINT_NAME
WHERE C.CONSTRAINT_TYPE = 'P' AND C.OWNER = 'SYS'
ORDER BY C.TABLE_NAME, D.POSITION