【分享】Mysql、SqlServer、Oracle、PostgreSQL、ClickHouse和PrestoDB获取元数据（表、字段信息）SQL_sqlserver元数据

前言：

工作中做数据同步的过程中，经常要获取表信息、表字段等元数据，做SQL解析，故整理一下常用的数据库获取元数据的SQL

这里的元数据指的是数据库内描述数据表和表字段信息的“身份证”

---

元数据结构定义：

元数据表结构定义TableMetadata

@Data
public class TableMetadata {
    private String tableSchema;
    private String tableName;
    private String tableComment;
}
1
2
3
4
5
6

元数据字段结构定义ColumnMetadata

@Data
public class ColumnMetadata {
    private String tableName;
    private String columnName;
    private String dataType;
    private String columnComment;
}
1
2
3
4
5
6
7

---

获取元数据：

提示：第一条sql是获取表元数据，第二条是获取字段元数据

Mysql 获取元数据

SELECT
	TABLE_NAME,
	TABLE_COMMENT,
	TABLE_SCHEMA
FROM
	INFORMATION_SCHEMA.TABLES
WHERE
	TABLE_SCHEMA = '%s'
1
2
3
4
5
6
7
8

SELECT
	TABLE_NAME,
	COLUMN_NAME,
	DATA_TYPE,
	COLUMN_COMMENT
FROM
	information_schema.COLUMNS
WHERE
	TABLE_SCHEMA = '%s'
1
2
3
4
5
6
7
8
9

---

SqlServer 获取元数据

SELECT name AS SCHEMA_NAME 
 FROM sys.schemas
SELECT
CONVERT(nvarchar(50),ISNULL(S.[name], '')) as TABLE_SCHEMA ,
CONVERT(nvarchar(50),ISNULL(A.[name], '')) as TABLE_NAME 
FROM sys.tables A
INNER JOIN sys.schemas S 
ON A.schema_id = S.schema_id  WHERE  S.name = '%s'
1
2
3
4
5
6
7
8

SELECT
	CONVERT(nvarchar(50),ISNULL(S.[name], '')) as schema_name ,
	CONVERT(nvarchar(50),ISNULL(A.[name], '')) as table_name ,
	CONVERT(nvarchar(50),ISNULL(B.[name], '')) as column_name,
	CONVERT(nvarchar(50),ISNULL(C.[value], '')) as column_comment,
	col.DATA_TYPE
	FROM sys.tables A
	INNER JOIN sys.schemas S 
	ON S.schema_id = A.schema_id 
	INNER JOIN sys.columns B 
	ON B.object_id = A.object_id
	INNER JOIN information_schema.columns col
	ON col.TABLE_SCHEMA  = S.name AND col.TABLE_NAME = A.name  AND col.COLUMN_NAME = B.name
	LEFT JOIN sys.extended_properties C 
	ON C.major_id = B.object_id 
	AND C.minor_id = B.column_id
	WHERE S.name = '%s'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

---

Oracle 获取元数据

SELECT
	TABLE_NAME,
	COMMENTS AS TABLE_COMMENT,
	'%s' AS TABLE_SCHEMA
FROM
	USER_TAB_COMMENTS
1
2
3
4
5
6

SELECT
	col.TABLE_NAME,
	col.COLUMN_NAME,
	col.DATA_TYPE,
	com.COMMENTS AS COLUMN_COMMENT
FROM
	user_tab_columns col
LEFT JOIN user_col_comments com ON
	col.TABLE_NAME = com.TABLE_NAME
	AND col.COLUMN_NAME = com.COLUMN_NAME
ORDER BY
	col.TABLE_NAME
1
2
3
4
5
6
7
8
9
10
11
12

---

PostgreSQL 获取元数据

SELECT
	C
	.relname AS tableName,
	ds.description AS tableComment
FROM
	(
	SELECT
		oid,
		relname,
		relnamespace
	FROM
		pg_class
	WHERE
		relkind = 'r'
		AND relname NOT LIKE 'pg_%'
		AND relname NOT LIKE 'sql_%' )
	C
LEFT JOIN pg_catalog.pg_namespace n ON
	C.relnamespace = n.oid
LEFT JOIN pg_catalog.pg_description ds ON
	ds.objoid = C.oid
	AND ds.objsubid = 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

SELECT
	C
	.relname AS tableName,
	A.attname AS columnName,
	T.typname AS dataType,
	dsc.description AS columnComment
FROM
	pg_catalog.pg_namespace n
JOIN (
	SELECT
		oid,
		relname,
		relnamespace
	FROM
		pg_class
	WHERE
		relkind = 'r'
		AND relname NOT LIKE 'pg_%'
		AND relname NOT LIKE 'sql_%' ) C ON
	( C.relnamespace = n.oid )
JOIN pg_catalog.pg_attribute A ON
	( A.attrelid = C.oid )
JOIN pg_catalog.pg_type T ON
	( A.atttypid = T.oid )
LEFT JOIN pg_catalog.pg_description dsc ON
	( C.oid = dsc.objoid
		AND A.attnum = dsc.objsubid )
WHERE
	A.attnum > 0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

---

ClickHouse 获取元数据

SELECT
	name AS TABLE_NAME,
	comment AS TABLE_COMMENT
FROM
	`SYSTEM`.tables
WHERE
	database = '%s'
1
2
3
4
5
6
7

SELECT
	TABLE AS TABLE_NAME,
	name AS COLUMN_NAME,
	TYPE AS DATA_TYPE,
	comment AS COLUMN_COMMENT
FROM
	`SYSTEM`.columns
WHERE
	database = '%s'
1
2
3
4
5
6
7
8
9

---

PrestoDB 获取源数据

SELECT
	TABLE_NAME,
	remarks AS TABLE_COMMENT,
	table_schem AS TABLE_SCHEMA
FROM
	system.jdbc.tables
WHERE
	table_cat = '%s'
	AND table_schem = '%s'
1
2
3
4
5
6
7
8
9

SELECT
	TABLE_NAME,
	COLUMN_NAME,
	DATA_TYPE
FROM
	system.jdbc.columns
WHERE
	table_cat = '%s'
	AND table_schem = '%s'
1
2
3
4
5
6
7
8
9

---