数据库中元数据提取

-- 系统表/视图
select * from syscomments;
select * from sysobjects;

select * from sys.objects;
select * from sys.schemas;
select * from sys.tables;
select * from sys.columns;
select * from sys.procedures;
select * from sys.indexes;
select * from sys.types;
select * from extended_properties;
-- 查看视图的定义
SELECT OBJECT_DEFINITION (OBJECT_ID('INFORMATION_SCHEMA.TABLES')) AS ObjectDefinition;   

-- 获取数据库的元数据
select * from INFORMATION_SCHEMA.SCHEMATA where SCHEMA_NAME not in ('INFORMATION_SCHEMA', 'sys');


-- 获取数据表元数据
SELECT
	tb.name,
	tb.create_date,
	tb.modify_date,
	tb.type_desc,
	sch.name AS schema_name,
	idx.rows,
	CONVERT(varchar(200), com.value) AS comment,
	(SELECT COUNT(name) FROM sys.columns col WHERE col.object_id = tb.object_id) AS fields
FROM
	sys.tables tb
	LEFT JOIN sys.schemas sch ON tb.schema_id = sch.schema_id
	LEFT JOIN sysindexes idx ON tb.object_id = idx.id AND (idx.indid IN ( 0, 1 ))
	LEFT JOIN sys.extended_properties com ON tb.object_id = com.major_id and com.minor_id = '0'




-- 获取数据字段的元数据
-- 方案一：
SELECT
	sch.name AS schema_name,
    tb.name AS table_name,
    col.name AS column_name,
    col.max_length AS length,
    col.is_nullable AS nullable,
		col.scale AS scale,
    t.name AS type,
    (
			SELECT TOP 1 ind.is_primary_key
			FROM sys.index_columns ic 
			LEFT JOIN sys.indexes ind ON ic.object_id = ind.object_id AND ic.index_id= ind.index_id AND ind.name LIKE 'PK_%'
			WHERE ic.object_id = tb.object_id AND ic.column_id= col.column_id
    ) AS isPrimaryKey,
    com.value AS comment
FROM
    sys.tables tb
    INNER JOIN sys.columns col ON col.object_id = tb.object_id
	LEFT JOIN sys.schemas sch ON tb.schema_id = sch.schema_id
    LEFT JOIN sys.types t ON t.user_type_id = col.user_type_id
    LEFT JOIN sys.extended_properties com ON com.major_id = col.object_id
    AND com.minor_id = col.column_id
		
-- 方案二：
select * from INFORMATION_SCHEMA.COLUMNS；





-- 获取函数/存储过程的元数据
SELECT
	obj.name,
	obj.type_desc,
	obj.create_date,
	obj.modify_date,
	syscom.ctext AS body, 
	sch.name AS schema_name,
	CONVERT(varchar(200), com.value) AS comment
FROM
	sys.objects obj
	LEFT JOIN syscomments syscom ON obj.object_id = syscom.id 
	LEFT JOIN sys.schemas sch ON obj.schema_id = sch.schema_id
	LEFT JOIN sys.extended_properties com ON obj.object_id = com.major_id
WHERE	obj.type IN ('P','FN');

-- DBA是只有DBA权限的用户才能查询，内容包含了系统全部；
-- ALL是普通用户可以查询，内容包含了该用户所能访问的部分；
-- User是普通用户可以查询，内容包含了该用户是所有者的部分；
-- V$是数据字典动态视图，显示系统当前的状态。它的值在随时都可能发生变化。
select * from all_users;
select * from all_tables where TABLESPACE_NAME not in('SYSTEM', 'SYSAUX');
select * from all_tab_comments;
select * from all_tab_cols;
select * from all_col_comments;
select * from all_source;

select * from user_tables;
select * from user_tab_comments; 
select * from user_tab_cols;
select * from user_col_comments;

-- 获取数据库元数据
select * from all_users;


-- 获取数据表元数据
SELECT
	tab.TABLE_NAME, tab.LAST_ANALYZED, tab.NUM_ROWS,
	(select count(*) from user_tab_columns where table_name=tab.TABLE_NAME) as fields,
	com.TABLE_TYPE,
	com.COMMENTS
FROM
	user_tables tab
	LEFT JOIN user_tab_comments com ON tab.TABLE_NAME = com.TABLE_NAME
	
	
-- 获取表字段元数据
SELECT
	col.*,
	com.COMMENTS 
FROM
	user_tab_cols col
	LEFT JOIN user_col_comments com ON col.TABLE_NAME = com.TABLE_NAME AND col.COLUMN_NAME = com.COLUMN_NAME;
	
	
-- 	获取函数元数据
SELECT
	* 
FROM
	user_source 
WHERE
	TYPE IN ( 'PROCEDURE', 'FUNCTION' )
	ORDER BY NAME ASC, type asc, line asc

-- SYSIBM: 基本系统编目,不建议直接访问
-- SYSCAT: 默认授权给Public组.只读编目视图,一般通过这个来获取编目信息
-- SYSSTAT: 可更新编目视图,会影响优化器的优化策略
-- SYSFUN: 用户定义函数
-- SYSPROC: 存放一组系统的存储过程

-- 获取系统用户的视图
SELECT * FROM SYSIBM.SYSTABLES WHERE NAME like 'USER_%';

-- 获取Schema元数据
select SCHEMANAME,OWNER from syscat.schemata;


-- 获取数据表元数据
SELECT
  TABSCHEMA, TABNAME, TYPE, CREATE_TIME, ALTER_TIME, COLCOUNT, CARD, REMARKS
FROM
  syscat.tables
where OWNER = CURRENT USER;


-- 获取表字段元数据
SELECT
  COL.TABSCHEMA AS TABLE_SCHEMA,
  COL.TABNAME AS TABLE_NAME,
  COL.COLNAME AS COLUMN_NAME,
  CAST(COL.COLNO + 1 AS SMALLINT) AS COLUMN_ID,
  COL.DEFAULT AS DATA_DEFAULT,
  COL.NUMNULLS AS NULLABLE,
  COL.REMARKS AS COMMENTS,
  COL.TYPENAME AS DATA_TYPE,
  CASE COL.TYPENAME
    WHEN 'GRAPHIC'
    THEN COL.LENGTH*2
    WHEN 'VARGRAPHIC'
    THEN COL.LENGTH*2
    WHEN 'LONG VARGRAPHIC'
    THEN COL.LENGTH*2
    WHEN 'DBCLOB'
    THEN COL.LENGTH*2
    ELSE COL.LENGTH
  END AS DATA_LENGTH,
  CASE COL.TYPENAME
    WHEN 'DECIMAL'
    THEN COL.LENGTH
  END AS DATA_PRECISION,
  CASE COL.TYPENAME
    WHEN 'CHAR'
    THEN COL.LENGTH
    WHEN 'VARCHAR'
    THEN COL.LENGTH
    WHEN 'LONG VARCHAR'
    THEN COL.LENGTH
  END AS CHAR_COL_DECL_LENGTH,
  COL.SCALE AS DATA_SCALE
FROM
  SYSCAT.COLUMNS AS COL,
  SYSCAT.TABLES AS TAB
WHERE
  COL.TABSCHEMA = TAB.TABSCHEMA
  AND TAB.TABNAME = COL.TABNAME
  AND TAB.TYPE NOT IN('A', 'V', 'W', 'N');


-- 	获取函数元数据
select
  ROUTINESCHEMA, SPECIFICNAME, ROUTINEMODULENAME, ROUTINETYPE, TEXT, OWNER, CREATE_TIME, ALTER_TIME
from syscat.routines
where ROUTINETYPE in ('F', 'P') and text IS NOT NULL and OWNER = CURRENT USER;