Trafodion 从元数据表中获取字段信息的相关总结

Trafodion中所有对象的元数据信息都保存在schema "_MD_"下面，对象的信息、字段的属性等。
这里我们描述几种常用的和获取字段信息有关的SQL方法。
测试表如下，

--创建测试表
DROP TABLE IF EXISTS aaa;
CREATE TABLE aaa (
	a NUMERIC(18, 8),
	b varchar2(10),
	c INT,
	d largeint,
	e CLOB,
	f DATE,
	g TIMESTAMP(6),
	h NUMERIC(20, 10),
	PRIMARY KEY (a, b)
);

CREATE UNIQUE INDEX idx_aaa ON aaa (c, d);

COMMENT ON TABLE aaa IS '表aaa的注释';
COMMENT ON COLUMN aaa.a IS '字段a的注释';
COMMENT ON COLUMN aaa.b IS '字段b的注释';
COMMENT ON COLUMN aaa.c IS '字段c的注释';
COMMENT ON COLUMN aaa.d IS '字段d的注释';
COMMENT ON COLUMN aaa.e IS '字段e的注释';
COMMENT ON COLUMN aaa.f IS '字段f的注释';
COMMENT ON COLUMN aaa.g IS '字段g的注释';
COMMENT ON COLUMN aaa.h IS '字段h的注释';

1. 获取字段详细信息

• SQL语句

SELECT a.schema_name, a.object_name AS table_name, b.column_name, b.sql_data_type AS data_type, b.column_size AS data_length
	, b.column_precision AS data_precision, b.nullable, b.column_number AS column_id, b.column_scale AS data_scale
	, CASE 
		WHEN c.column_name IS NOT NULL THEN 'Y'
		ELSE 'N'
	END AS is_pk
	, CASE 
		WHEN g.column_name IS NOT NULL THEN 'Y'
		ELSE 'N'
	END AS is_unique
	, h.text AS comments
FROM "_MD_".objects a
	INNER JOIN "_MD_".columns b ON a.object_uid = b.object_uid
	LEFT JOIN "_MD_".keys c
	ON b.object_uid = c.object_uid
		AND b.column_name = c.column_name
	LEFT JOIN (SELECT d.base_table_uid,  rtrim(f.column_name,'@') AS column_name
		FROM "_MD_".indexes d
			INNER JOIN "_MD_".objects e ON d.index_uid = e.object_uid
			INNER JOIN "_MD_".columns f ON e.object_uid = f.object_uid
		WHERE d.is_unique = 1
		  AND f.column_name <> 'SYSKEY') g
	ON a.object_uid = g.base_table_uid
	    AND b.column_name = g.column_name
	LEFT JOIN "_MD_".text h 
	ON b.object_uid = h.text_uid
		AND b.column_number = h.sub_id
		AND h.text_type = 12
WHERE a.schema_name NOT LIKE '|_BACKUP|_%%|_' ESCAPE '|'
AND a.schema_name NOT IN ('_MD_', '_PRIVMGR_MD_', '_REPOS_', '_LIBMGR_')
AND a.object_type IN ('BT')
AND a.object_name NOT IN ('SB_HISTOGRAMS', 'SB_HISTOGRAM_INTERVALS', 'SB_PERSISTENT_SAMPLES')
AND b.column_name <> 'SYSKEY'
AND a.schema_name = 'SEABASE'
AND a.object_name = 'AAA'
;

• 样例输出

SCHEMA_NAME	TABLE_NAME	COLUMN_NAME	DATA_TYPE	DATA_LENGTH	DATA_PRECISION	NULLABLE	COLUMN_ID	DATA_SCALE	IS_PK	IS_UNIQUE	COMMENTS
SEABASE	AAA	A	SIGNED LARGEINT	8	18	0	0	8	Y	Y	字段a的注释
SEABASE	AAA	H	SIGNED NUMERIC	10	20	1	7	10	N	N	字段h的注释
SEABASE	AAA	G	DATETIME	11	0	1	6	6	N	N	字段g的注释
SEABASE	AAA	F	DATETIME	4	0	1	5	0	N	N	字段f的注释
SEABASE	AAA	E	CLOB	1024	1	1	4	1073741824	N	N	字段e的注释
SEABASE	AAA	D	SIGNED LARGEINT	8	0	1	3	0	N	Y	字段d的注释
SEABASE	AAA	C	SIGNED INTEGER	4	0	1	2	0	N	Y	字段c的注释
SEABASE	AAA	B	VARCHAR	10	0	0	1	0	Y	Y	字段b的注释

2. 获取主键字段信息

• SQL语句

SELECT a.schema_name, a.object_name AS table_name, b.column_name
	, CASE 
		WHEN c.column_name IS NOT NULL THEN 'Y'
		ELSE 'N'
	END AS is_pk
FROM "_MD_".objects a
	INNER JOIN "_MD_".columns b ON a.object_uid = b.object_uid
	LEFT JOIN "_MD_".keys c
	ON b.object_uid = c.object_uid
		AND b.column_name = c.column_name
WHERE a.schema_name NOT LIKE '|_BACKUP|_%%|_' ESCAPE '|'
AND a.schema_name NOT IN ('_MD_', '_PRIVMGR_MD_', '_REPOS_', '_LIBMGR_')
AND a.object_type IN ('BT')
AND a.object_name NOT IN ('SB_HISTOGRAMS', 'SB_HISTOGRAM_INTERVALS', 'SB_PERSISTENT_SAMPLES')
AND b.column_name <> 'SYSKEY'
AND c.column_name IS NOT NULL
AND a.schema_name = 'SEABASE'
AND a.object_name = 'AAA'
;

• 样例输出

SCHEMA_NAME	TABLE_NAME	COLUMN_NAME	IS_PK
SEABASE	AAA	A	Y
SEABASE	AAA	B	Y

3. 获取固定表名所在的schema

SQL语句

SELECT a.schema_name
FROM "_MD_".objects a
WHERE a.object_name = 'AAA'
;

样例输出

SCHEMA_NAME
SEABASE

4. 获取指定shema名称

• SQL语句

SELECT distinct a.schema_name
FROM "_MD_".objects a
WHERE a.schema_name = 'V7DEV'
;

• 样例输出

SCHEMA_NAME
V7DEV

5. 获取固定表名的字段是否唯一信息

• SQL语句

SELECT a.schema_name, a.object_name AS table_name, b.column_name
	, CASE 
		WHEN instr(g.column_list, b.column_name) > 0 THEN 'Y'
		ELSE 'N'
	END AS is_unique
FROM "_MD_".objects a
	INNER JOIN "_MD_".columns b ON a.object_uid = b.object_uid
	LEFT JOIN "_MD_".keys c
	ON b.object_uid = c.object_uid
		AND b.column_name = c.column_name
	LEFT JOIN seabase.tbl_column_list g
	ON a.object_uid = g.base_table_uid
WHERE a.schema_name NOT LIKE '|_BACKUP|_%%|_' ESCAPE '|'
AND a.schema_name NOT IN ('_MD_', '_PRIVMGR_MD_', '_REPOS_', '_LIBMGR_')
AND a.object_type IN ('BT')
AND a.object_name NOT IN ('SB_HISTOGRAMS', 'SB_HISTOGRAM_INTERVALS', 'SB_PERSISTENT_SAMPLES')
AND b.column_name <> 'SYSKEY'
AND a.schema_name = 'SEABASE'
AND a.object_name = 'AAA'
;  

• 样例输出

SCHEMA_NAME	TABLE_NAME	COLUMN_NAME	IS_UNIQUE
SEABASE	AAA	A	Y
SEABASE	AAA	B	Y
SEABASE	AAA	C	Y
SEABASE	AAA	D	Y
SEABASE	AAA	E	N
SEABASE	AAA	F	N
SEABASE	AAA	G	N
SEABASE	AAA	H	N

6. 获取固定schema下所有表的行数

• SQL语句

--创建函数，用于统计固定表名的行数
CREATE OR REPLACE FUNCTION count_rows(table_name varchar2(200), 
schema_name varchar2(200)  
) RETURN (rowcount INT)
AS
DECLARE
  rowcount number;
  stmt     varchar2(2000);
BEGIN
  if schema_name is null then
    stmt := 'select count(*) from "' || table_name || '"';
  else
    stmt := 'select count(*) from "' || schema_name || '"."' || table_name || '"';
  end if;
PREPARE s1 FROM stmt;
execute s1 into rowcount;
return rowcount;
END;
/

SELECT a.schema_name, a.object_name AS table_name, seabase.count_rows(a.object_name, a.schema_name) AS rowcount
FROM "_MD_".objects a
WHERE a.object_name NOT IN ('SB_HISTOGRAMS', 'SB_HISTOGRAM_INTERVALS', 'SB_PERSISTENT_SAMPLES')
AND a.object_name NOT LIKE 'LOBDescChunks%'
AND a.object_name NOT LIKE 'LOBMD%'
AND a.object_name NOT LIKE 'LOBDescHandle%'
AND a.object_type IN ('BT')
AND a.schema_name = 'SEABASE'
;       

• 样例输出

SCHEMA_NAME |TABLE_NAME      |ROWCOUNT 
------------|----------------|---------
SEABASE     |AAA             |0        
SEABASE     |TBL_COLUMN_LIST |1347  

7. 获取固定schema下每个表的字段列表

• SQL语句

--字段拼接不考虑顺序
SELECT a.schema_name, a.object_name AS table_name, pivot(b.column_name, MAX LENGTH 10240) AS column_list
FROM "_MD_".objects a
	INNER JOIN "_MD_".columns b ON a.object_uid = b.object_uid
WHERE a.schema_name NOT LIKE '|_BACKUP|_%%|_' ESCAPE '|'
AND a.schema_name NOT IN ('_MD_', '_PRIVMGR_MD_', '_REPOS_', '_LIBMGR_')
AND a.object_type IN ('BT')
AND a.object_name NOT IN ('SB_HISTOGRAMS', 'SB_HISTOGRAM_INTERVALS', 'SB_PERSISTENT_SAMPLES')
AND b.column_name <> 'SYSKEY'
AND a.schema_name = 'SEABASE'
AND a.object_name = 'AAA'
GROUP BY 1,2
;
--字段拼接考虑顺序
SELECT a.schema_name, a.object_name AS table_name, pivot(b.column_name, MAX LENGTH 10240, ORDER BY (b.column_number)) AS column_list
FROM "_MD_".objects a
	INNER JOIN "_MD_".columns b ON a.object_uid = b.object_uid
WHERE a.schema_name NOT LIKE '|_BACKUP|_%%|_' ESCAPE '|'
AND a.schema_name NOT IN ('_MD_', '_PRIVMGR_MD_', '_REPOS_', '_LIBMGR_')
AND a.object_type IN ('BT')
AND a.object_name NOT IN ('SB_HISTOGRAMS', 'SB_HISTOGRAM_INTERVALS', 'SB_PERSISTENT_SAMPLES')
AND b.column_name <> 'SYSKEY'
AND a.schema_name = 'SEABASE'
AND a.object_name = 'AAA'
GROUP BY 1,2
;

• 样例输出

SCHEMA_NAME	TABLE_NAME	COLUMN_LIST
SEABASE	AAA	A,B,C,D,E,F,G,H