获取数据库元数据

关键字 数据库 对象
原作者姓名 Fang

SQL SERVER
取得数据库名的方法
系统存储过程sp_helpdb

或者

USE master

SELECT sysdb.name AS dbname, xlogin.name AS owner FROM sysdatabases AS sysdb LEFT JOIN sysxlogins AS xlogin ON sysdb.sid = xlogin.sid


sp_helpdb获得的内容更准确：第二种方式无法获取Windows NT登陆用户。

取得表的方法
系统存储过程

USE xxx

EXEC sp_help

或者

获取指定数据库的用户表：

USE DBAudit
SELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'U'

获取指定数据库的系统表：

USE DBAudit
SELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'S'

或者统一使用：

USE DBAudit

SELECT sysobjects.name AS name, sysusers.name AS owner, type, sysobjects.id AS id FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'U' OR type = 'S'

注：

sysobjects中type字段类型解释：

C = CHECK 约束

D = 默认值或 DEFAULT 约束

F = FOREIGN KEY 约束

L = 日志

FN = 标量函数

IF = 内嵌表函数

P = 存储过程

PK = PRIMARY KEY 约束（类型是 K）

RF = 复制筛选存储过程

S = 系统表

TF = 表函数

TR = 触发器

U = 用户表

UQ = UNIQUE 约束（类型是 K）

V = 视图

X = 扩展存储过程

取得列的方法
系统存储过程

USE xxxDatabase

EXEC sp_help xxxTable

或者

USE xxxDatabase

SELECT syscolumns.name AS name, systypes.name AS type FROM syscolumns LEFT JOIN systypes ON syscolumns.xtype = systypes.xtype WHERE syscolumns.id = xxxTableId

这两种方法都可以取得包括视图的列。

取得视图的方法
系统存储过程

USE xxx

EXEC sp_help

或者

获取指定数据库的视图：

USE DBAudit

SELECT sysobjects.name AS name, sysusers.name AS owner FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'V'

获取指定数据库的视图的内容：

EXEC sp_helptext xxxObject

取得存储过程的方法
系统存储过程

USE xxx

EXEC sp_help

或者

获取指定数据库的存储过程或者扩展存储过程：

USE DBAudit

SELECT sysobjects.name AS name, sysusers.name AS owner, type FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'P' OR type = 'X'

获取指定数据库的存储过程或者扩展存储过程的内容：

EXEC sp_helptext xxxObject

无法获取加密的存储过程。扩展存储过程只能得到dll程序名。

取得函数的方法
系统存储过程

USE xxx

EXEC sp_help

或者

获取指定数据库的各种函数（内嵌函数等）：

USE DBAudit

SELECT sysobjects.name AS name, sysusers.name AS owner, type FROM sysobjects LEFT JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE type = 'FN' OR type = 'IF' OR type = ‘TF'

获取指定数据库的函数的内容：

EXEC sp_helptext xxxObject

只能获取用户定义的函数内容。

取得触发器的方法
系统存储过程

USE xxx

EXEC sp_helptrigger xxxTable

或者

获取指定数据库的表的触发器：

SELECT sysobj1.name AS name, sysusers.name AS owner FROM sysobjects AS sysobj1 LEFT JOIN sysobjects AS sysobj2 ON sysobj1.parent_obj = sysobj2.id LEFT JOIN sysusers ON sysobj1.uid = sysusers.uid WHERE sysobj1.type = 'TR'AND sysobj2.name = 'xxxTable'

取得索引的方法
系统存储过程

USE xxx

EXEC sp_helpindex xxxTable

用户
系统存储过程

USE xxx

EXEC sp_helpuser

角色
系统存储过程

USE xxx
EXEC sp_helprole

 

ORACLE
特殊
Ø ORACLE只能连接特定数据库，不能自动扫描数据库对象。

Ø ORACLE表必须用用户来区别，否则表可能重名。

Ø ORACLE不能区别系统表或者是用户表，因此只能由管理员选择扫描某些特定用户的表。

取得数据库名的方法
只能由用户指定数据库，不能自动扫描到特定数据库服务器上的所有数据库。

取得表的方法
存储数据库表的系统表/视图主要有：

DBA_ALL_TABLES

描述数据库中所有的对象以及相关的表。

ALL_ALL_TABLES

描述数据库中所有的用户可以访问的对象以及相关的表。

USER_ALL_TABLES

描述数据库中当前用户拥有的对象以及相关的表。


DBA_ TABLES

描述数据库中所有相关的表。

ALL_ TABLES

描述数据库中所有的用户可以访问的相关的表。

USER_ TABLES

描述数据库中当前用户拥有的相关的表。


因此，可以从ALL_TABLES中取得隶属于指定用户的表：

SELECT TABLE_NAME FROM SYS.ALL_TABLES WHERE OWNER = 'DBAUDIT';

取得隶属于指定表空间的表：

SELECT TABLE_NAME FROM SYS.ALL_TABLES WHERE TABLESPACE_NAME = 'DBAUDIT';


也可以不指定用户名，从而取得所有的表。

SELECT TABLE_NAME FROM SYS.ALL_TABLES；

取得列的方法
存储数据库表的列属性的系统表/视图主要有：

DBA_TAB_COLUMNS
描述数据库中所有的表的列属性。
ALL_TAB_COLUMNS
描述数据库中所有的用户可以访问的表的列属性。
USER_TAB_COLUMNS
描述数据库中当前用户拥有的表的列属性。
ALL_TAB_COLS

描述数据库中所有的用户可以访问的表的列属性。


因此，可以从ALL_TAB_COLUMNS中取得隶属于指定用户的表：
SELECT COLUMN_NAME FROM SYS.ALL_TAB_COLUMNS WHERE TABLE_NAME = 'TASK' AND OWNER = 'DBAUDIT';

取得视图的方法
存储数据库视图的系统表/视图主要有：

DBA_VIEWS
描述数据库中所有的视图的属性。
ALL_VIEWS
描述数据库中所有的用户可以访问的视图的属性。
USER_VIEWS

描述数据库中所有的当前用户拥有视图的属性。


因此，可以从ALL_VIEWS中取得隶属于指定用户的视图：
SELECT VIEW_NAME FROM SYS.ALL_VIEWS WHERE OWNER = 'DBAUDIT';

取得存储过程的方法
存储系统对象的系统表/视图有：

DBA_OBJECTS

描述数据库中所有的对象。

ALL_OBJECTS
描述数据库中所有的可以访问的对象。
USER_OBJECTS

描述数据库中所有的当前用户拥有的对象。

SYS_OBJECTS

描述数据库中所有的系统对象。？


对象类型有：

CONSUMER GROUP

CONTEXT

DIRECTORY

FUNCTION

INDEX

INDEX PARTITION

INDEXTYPE

JAVA CLASS

JAVA DATA

JAVA RESOURCE

JAVA SOURCE

LIBRARY

LOB

MATERIALIZED VIEW

OPERATOR

PACKAGE

PACKAGE BODY

PROCEDURE

QUEUE

SEQUENCE

SYNONYM

TABLE

TABLE PARTITION

TRIGGER

TYPE

TYPE BODY

VIEW


因此，取得存储过程可以用：

SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE';

取得隶属于某个用户的存储过程可以用：

SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE = 'PROCEDURE' AND OWNER = ‘DBAUDIT';


同样，取得其他的对象也可以用这个方法，包括表，视图等。

取得存储过程内容的方法
对象类型为类型、类型体、过程、函数、包、包体，JAVA源代码的所有对象的源代码都存储在几个系统表/视图中：

DBA_SOURCE

存储所有数据库对象的源代码。

ALL_SOURCE
存储所有可以访问的数据库对象的源代码。
USER_SOURCE

存储所有当前用户拥有的数据库对象的源代码。


因此，取得存储过程源代码的方法：

SELECT TEXT FROM SYS.ALL_SOURCE WHERE NAME = ‘XXX' AND TYPE = ‘PROCEDURE';

取得函数的方法
同上，取得函数可以用：

SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION';

取得隶属于某个用户的函数可以用：

SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE = 'FUNCTION' AND OWNER = 'DBAUDIT';


取得函数内容可以用：

SELECT TEXT FROM SYS.ALL_SOURCE WHERE NAME = 'XXX' AND TYPE = 'FUNCTION';

取得触发器的方法
存储数据库触发器的系统表/视图主要有：

DBA_TRIGGERS
描述数据库中所有的触发器的属性。
ALL_ TRIGGERS
描述数据库中所有的用户可以访问的触发器的属性。
USER_ TRIGGERS

描述数据库中所有的当前用户拥有触发器的属性。


因此，可以从ALL_ TRIGGERS中取得隶属于指定用户的触发器：
SELECT TRIGGER_NAME FROM SYS.ALL_TRIGGERS WHERE OWNER = 'DBAUDIT';


取得触发器内容的方法：

SELECT TRIGGER_BODY FROM SYS.ALL_TRIGGERS WHERE TRIGGER_NAME = 'XXX';

取得索引的方法
存储数据库索引的系统表/视图主要有：

DBA_INDEXES
描述数据库中所有的索引的属性。
ALL_ INDEXES
描述数据库中所有的用户可以访问的索引的属性。
USER_ INDEXES

描述数据库中所有的当前用户拥有索引的属性。


因此，可以从ALL_ INDEXES中取得隶属于指定用户的索引：
SELECT INDEX_NAME,TABLE_NAME FROM SYS.ALL_INDEXES WHERE OWNER = 'DBAUDIT';


取得索引相关的列的方法：

SELECT COLUMN_NAME FROM SYS.ALL_IND_COLUMNS WHERE INDEX_NAME = 'XXX' AND TABLE_OWNER = 'DBAUDIT';

用户
存储数据库用户的系统表/视图主要有：

DBA_USERS
描述数据库中所有的用户的属性。
ALL_USERS
描述数据库中所有的用户的属性。
USER_USERS

描述数据库中当前用户的属性。


因此，可以从ALL_USERS中取得用户：
SELECT USER_ID, USERNAME FROM SYS.ALL_USERS;

角色
存储角色的表/视图有：

DBA_ROLES

描述数据库中所有角色。


取得角色方法：

SELECT ROLE FROM SYS.DBA_ROLES;

表空间
SELECT TABLESPACE_NAME FROM SYS.DBA_TABLESPACES;

数据文件
SELECT FILE_NAME, FILE_ID, TABLESPACE_NAME FROM SYS.DBA_DATA_FILES;

数据库连接
存储数据库连接的系统表/视图主要有：

DBA_LINKS
描述数据库中所有的连接的属性。
ALL_LINKS
描述数据库中所有的用户可访问的连接的属性。
USER_LINKS

描述数据库中当前用户的连接属性。


因此，可以从DBA_DB_LINKS中取得连接：
SELECT * FROM SYS.DBA_DB_LINKS;


同义词
SELECT * FROM SYS.ALL_SYNONYMS WHERE TABLE_OWNER = 'DBAUDIT';

程序包
取得程序包可以用：

SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE';

取得隶属于某个用户的程序包可以用：

SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE' AND OWNER = 'DBAUDIT';


取得程序包内容可以用：

SELECT TEXT FROM SYS.ALL_SOURCE WHERE NAME = 'XXX' AND TYPE = 'PACKAGE';

程序包体
取得程序包体可以用：

SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE BODY';

取得隶属于某个用户的程序包可以用：

SELECT OBJECT_NAME FROM SYS.ALL_OBJECTS WHERE OBJECT_TYPE = 'PACKAGE BODY ' AND OWNER = 'DBAUDIT';


取得程序包内容可以用：

SELECT TEXT FROM SYS.ALL_SOURCE WHERE NAME = 'XXX' AND TYPE = 'PACKAGE BODY ';

簇
暂不扫描。

高级队列
暂不扫描。

序列
暂不扫描。

数组类型
暂不扫描。

维
暂不扫描。

Java源
暂不扫描。

对象类型
暂不扫描。

刷新组
暂不扫描。

概要文件
暂不扫描。

Sybase
取得数据库名的方法
系统存储过程sp_helpdb

或者

USE master

SELECT d.name dbname, u.name owner FROM sysdatabases d, sysusers u WHERE d.suid = u.suid


取得表的方法
系统存储过程

USE xxx

EXEC sp_help

或者

获取指定数据库的用户表：

USE DBAudit
SELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects, sysusers WHERE sysobjects.uid = sysusers.uid AND type = 'U'

获取指定数据库的系统表：

USE DBAudit
SELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects, sysusers WHERE sysobjects.uid = sysusers.uid AND type = 'S'

或者统一使用：

USE DBAudit

SELECT sysobjects.name AS tname, sysusers.name AS owner FROM sysobjects, sysusers WHERE sysobjects.uid = sysusers.uid AND (type = 'U' OR type = 'S')

注：

sysobjects中type字段类型解释：

C = CHECK 约束

D = 默认值或 DEFAULT 约束

F = FOREIGN KEY 约束

L = 日志

FN = 标量函数

IF = 内嵌表函数

P = 存储过程

PK = PRIMARY KEY 约束（类型是 K）

RF = 复制筛选存储过程

S = 系统表

TF = 表函数

TR = 触发器

U = 用户表

UQ = UNIQUE 约束（类型是 K）

V = 视图

X = 扩展存储过程

取得列的方法
系统存储过程

USE xxxDatabase

EXEC sp_help xxxTable

取得视图的方法
系统存储过程

USE xxx

EXEC sp_help

或者

获取指定数据库的视图：

USE xxx

SELECT sysobjects.name AS name, sysusers.name AS owner FROM sysobjects, sysusers WHERE sysobjects.uid = sysusers.uid AND type = 'V'

获取指定数据库的视图的内容：

EXEC sp_helptext xxxObject

取得存储过程的方法
系统存储过程

USE xxx

EXEC sp_help

或者

获取指定数据库的存储过程或者扩展存储过程：

USE xxx

SELECT sysobjects.name name, sysusers.name owner FROM sysobjects, sysusers WHERE sysobjects.uid = sysusers.uid AND (type = 'P' OR type = 'X')

获取指定数据库的存储过程或者扩展存储过程的内容：

EXEC sp_helptext xxxObject

无法获取加密的存储过程。扩展存储过程只能得到dll程序名。

取得函数的方法
Sybase不支持用户定义函数。

取得触发器的方法
获取指定数据库的表的触发器：


取得索引的方法
系统存储过程

USE xxx

EXEC sp_helpindex xxxTable

用户
系统存储过程

USE xxx

EXEC sp_helpuser

角色
DB2
特殊
Ø DB2只能连接特定数据库，不能自动扫描数据库对象。

Ø DB2表必须用用户来区别，否则表可能重名。

Ø DB2不能区别系统表或者是用户表，因此只能由管理员选择扫描某些特定用户的表。

取得数据库名的方法
只能由用户指定数据库，不能自动扫描到特定数据库服务器上的所有数据库。

取得表的方法
SELECT NAME, CREATOR FROM SYSIBM.SYSTABLES WHERE TYPE = 'T'

或者

SELECT TABNAME, TABSCHEMA FROM SYSCAT.TABLES WHERE TYPE = 'T'

取得列的方法
SELECT NAME, COLTYPE FROM SYSIBM.SYSCOLUMNS WHERE TBNAME = 'SYSTABLES' AND TBCREATOR = 'SYSIBM'

或者

SELECT COLNAME, TYPENAME FROM SYSCAT.COLUMNS WHERE TABNAME = 'SYSTABLES' AND TABSCHEMA = 'SYSIBM'

取得视图的方法
SELECT NAME, CREATOR FROM SYSIBM.SYSTABLES WHERE TYPE = 'V'

或者

SELECT NAME, CREATOR FROM SYSIBM.SYSVIEWS
或者
SELECT TABNAME, TABSCHEMA FROM SYSCAT.TABLES WHERE TYPE = 'V'
或者
SELECT VIEWNAME, VIEWSCHEMA FROM SYSCAT.VIEWS
取得内容的方法
SELECT TEXT FROM SYSCAT.VIEWS WHERE VIEWNAME = 'VIEWS' AND VIEWSCHEMA = 'SYSCAT'

取得存储过程的方法
SELECT PROCNAME, PROCSCHEMA FROM SYSIBM.SYSPROCEDURES

取得存储过程内容的方法

SELECT TEXT FROM SYSIBM.SYSPROCEDURES WHERE PROCNAME = 'XXX' AND PROCSCHEMA = 'YYY'

取得函数的方法
SELECT NAME, SCHEMA FROM SYSIBM.SYSFUNCTIONS

取得函数内容可以用：


取得触发器的方法
SELECT NAME, SCHEMA FROM SYSIBM.SYSTRIGGERS WHERE TBNAME = 'SYSTABLES' AND TBCREATOR = 'SYSIBM'
或者
SELECT TRIGNAME, TRIGSCHEMA FROM SYSCAT.TRIGGERS WHERE TABNAME = 'SYSTABLES' AND TABSCHEMA = 'SYSIBM'

取得触发器内容的方法：

SELECT TEXT FROM SYSIBM.SYSTRIGGERS WHERE NAME = 'XXX' AND SCHEMA = 'YYY '

取得索引的方法
SELECT NAME, CREATOR, COLNAMES FROM SYSIBM.SYSINDEXES WHERE TBNAME = 'SYSTABLES' AND TBCREATOR = 'SYSIBM'

或者

SELECT INDNAME, INDSCHEMA, COLNAMES FROM SYSCAT.INDEXES WHERE TABNAME = 'SYSTABLES' AND TABSCHEMA = 'SYSIBM'

用户
SELECT GRANTEE FROM SYSIBM.SYSDBAUTH WHERE GRANTEETYPE = 'U'
或者
SELECT GRANTEE FROM SYSCAT.DBAUTH WHERE GRANTEETYPE = 'U'

角色
DB2只有用户组的概念。

SELECT GRANTEE FROM SYSIBM.SYSDBAUTH WHERE GRANTEETYPE = 'G'
或者
SELECT GRANTEE FROM SYSCAT.DBAUTH WHERE GRANTEETYPE = 'G'

表空间
SELECT TBSPACE FROM SYSCAT.TABLESPACES

数据库连接

同义词

Informix
取得数据库名的方法
DATABASE sysmaster

SELECT name, owner FROM sysdatabases

取得表的方法
获取指定数据库的表：

DATABASE xxx
SELECT tabname, owner, tabid FROM systables WHERE tabtype = 'T'

注：

tabid 为0-24为系统表，100-xxx为用户表。

tabtype字段类型解释：

T = 用户表

V = 视图

P = 私有同义词

S = 同义词

取得列的方法
DATABASE xxxDatabase
SELECT c.colname, c.coltype FROM syscolumns c, systables t WHERE c.tabid = t.tabid AND t.tabname = 'xxxTable'
可以取得包括视图的列。
注：
coltype 字段意义：
0 = CHAR8 = MONEY
1 = SMALLINT10 = DATETIME
2 = INTEGER 11 = BYTE
3 = FLOAT 12 = TEXT
4 = SMALLFLOAT 13 = VARCHAR
5 = DECIMAL14 = INTERVAL
6 = SERIAL15 = NCHAR
7 = DATE 16 = NVARCHAR


取得视图的方法
获取指定数据库的视图：

DATABASE xxx
SELECT tabname, owner FROM systables WHERE tabtype = 'V'

取得视图内容：

SELECT v.viewtext FROM sysviews v, systables t WHERE v.tabid = t.tabid AND t.tabname = 'xxxTable'

取得存储过程的方法
获取指定数据库的存储过程：

DATABASE xxx
SELECT procname, owner FROM sysprocedures
获取指定数据库的存储过程的内容：
SELECT b.data FROM sysprocbody b, sysprocedures p WHERE b.procid = p.procid AND p.procname = 'systdist' AND datakey = 'T'

取得函数的方法
Informix不支持用户定义函数?

取得触发器的方法
获取指定数据库的表的触发器：

SELECT g.trigname, g.owner, g.event FROM systriggers g, systables t WHERE g.tabid = t.tabid AND t.tabname = 'xxxTable'

取得触发器内容：

SELECT b.data FROM systrigbody b, systriggers g WHERE b.trigid = g.trigid AND g.trigname = '%s' AND datakey = 'D'

取得索引的方法
获取指定数据库的表的触发器：

SELECT i.idxname FROM sysindexes i, systables t WHERE i.tabid = t.tabid AND t.tabname = 'xxxTable'