最近在开发数据库元数据抽取的功能，鉴于自己刚刚开始学习元数据查询，在开发的过程中遇到了许多问题，针对这些问题，在网站参考了许多前辈的经验，下面把开发中使用到的sql语句记录下来，希望对大家有帮助。?
|
1
2
3
4
5
6
|
--获取视图信息和视图描述信息commentsSELECT
C.COMMENTS, T.*
FROM
ALL_VIEWS T, ALL_TAB_COMMENTS C
WHERE
1 = 1
AND
T.VIEW_NAME = C.TABLE_NAME
AND
T.VIEW_NAME = ‘VIEW_NAME‘
|
|
1
2
3
4
5
6
7
8
9
10
|
--获取到视图相关的字段，并查询出字段是否可插入(insertable)、可更新(updatable)、可删除(deletable)并查询出注释信息comments SELECT
C.COMMENTS, T.*, U.UPDATABLE, U.INSERTABLE, U.DELETABLE
FROM
ALL_TAB_COLUMNS T, ALL_UPDATABLE_COLUMNS U, ALL_COL_COMMENTS C
WHERE
1 = 1
AND
U.TABLE_NAME = T.TABLE_NAME
AND
U.COLUMN_NAME = T.COLUMN_NAME
AND
C.TABLE_NAME = T.TABLE_NAME
AND
C.COLUMN_NAME = T.COLUMN_NAME
AND
T.TABLE_NAME = ‘VIEWNAME‘
|
|
1
2
3
4
5
|
--查询出视图和表的依赖信息dependenciesSELECT
C.*
FROM
ALL_DEPENDENCIES C
WHERE
1 = 1
AND
C.NAME = ‘VIEWNAME‘
|
|
1
2
3
4
5
6
|
--获取数据库中的表信息和表注释commentsSELECT C.COMMENTS, T.*
FROM ALL_TABLES T, ALL_TAB_COMMENTS C
WHERE 1 = 1
AND T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_NAME = ‘TABLE_NAME‘
|
|
1
2
3
4
5
6
|
--查询出表中的列信息，包括列的注释commentsSELECT
T.*, C.COMMENTS
FROM
ALL_TAB_COLUMNS T, ALL_COL_COMMENTS C
WHERE
T.TABLE_NAME = C.TABLE_NAME
AND
C.COLUMN_NAME = T.COLUMN_NAME
AND
T.TABLE_NAME = ‘TABLE_NAME‘
|
|
1
2
3
4
5
6
|
--查询出表的主键信息和主键所在的列名称column_nameSELECT
A.COLUMN_NAME, C.*
FROM
ALL_CONSTRAINTS C, ALL_CONS_COLUMNS A
WHERE
C.CONSTRAINT_TYPE = ‘P‘
AND
C.CONSTRAINT_NAME = A.CONSTRAINT_NAME
AND
C.TABLE_NAME = ‘TABLE_NAME‘
|
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
--获取到外键的信息，包括外键所在的列名称和外键所参照的主键的表名称和主键所在的列的信息SELECT
T2.R_COLUMN_NAME, T2.R_TABLE_NAME, T1.*
FROM
（SELECT A1.COLUMN_NAME, C.*
FROM
ALL_CONSTRAINTS C, ALL_CONS_COLUMNS A1
WHERE
C.CONSTRAINT_TYPE = ‘R‘
AND
C.TABLE_NAME = ‘TABLE_NAME‘
AND
A1.CONSTRAINT_NAME = C.CONSTRAINT_NAME）T1,
(SELECT
A1.COLUMN_NAME AS
R_COLUMN_NAME,
A1.TABLE_NAME AS
R_TABLE_NAME,
C.*
FROM
ALL_CONSTRAINTS C, ALL_CONS_COLUMNS A1
WHERE
A1.CONSTRAINT_NAME = C.CONSTRAINT_NAME) T2
WHERE
T1.R_CONSTRAINT_NAME = T2.CONSTRAINT_NAME
|
|
1
2
3
4
5
|
--查询出表相关的检查约束信息SELECT
C.*
FROM
ALL_CONSTRAINTS C
WHERE
C.CONSTRAINT_TYPE = ‘C‘
AND
C.TABLE_NAME = ‘TABLE_NAME‘
|
|
1
2
3
4
5
|
--查询出表相关的唯一约束信息SELECT
C.*
FROM
ALL_CONSTRAINTS C
WHERE
C.CONSTRAINT_TYPE = ‘U‘
AND
C.TABLE_NAME = ‘TABLE_NAME‘
|
|
1
2
3
4
5
|
--表中的索引信息SELECT
C.*
FROM
ALL_INDEXES C
WHERE
1 = 1
AND
C.TABLE_NAME = ‘TABLE_NAME‘
|
|
1
2
3
4
5
|
--表与视图的依赖信息SELECT
C.*
FROM
ALL_DEPENDENCIES C
WHERE
1 = 1
AND
C.REFERENCED_NAME = ‘TABLE_NAME‘
|
同时，数据字典中的前缀是可以根据查询的权限范围进行修改的，例USER_ ALL_ DBA_ 这三种的范围是由小到大的范围，