MySQL、SQLServer、PostgreSql、Oracle中查看所有表和字段以及表注释、字段注释

MySQL中查询的操作

查询所有数据库

show databases;

查询指定数据库中所有表名和注释

SELECT
	table_name,
	table_comment AS description
FROM
	information_schema.TABLES
WHERE
	table_schema = '表名'
	AND table_type = 'BASE TABLE';

查询指定表中的所有字段名和字段类型

SELECT
	 column_name,
	 column_comment AS ,
	data_type 
FROM
	information_schema.COLUMNS
WHERE
	table_schema = 'dynamic_data_source'
	AND table_name = 'data_source';

SQLServer中查询的操作

查询当前数据库中所有表名

SELECT
	sob.name AS table_name,
	CONVERT (
		VARCHAR ( 100 ),
		(
		SELECT
			ds.value 
		FROM
			sysobjects tbs
			LEFT JOIN sys.extended_properties ds ON ds.major_id= tbs.id 
		WHERE
			ds.minor_id= 0 
			AND tbs.name = sob.name 
		) 
	) AS description 
FROM
	SysObjects sob 
WHERE
	XType = 'U' 
ORDER BY
	sob.name;

根据表名称查询所有字段和注释

SELECT
	a.name column_name,
	isnull( g.[value], ' ' ) AS description
FROM
	syscolumns a
	LEFT JOIN systypes b ON a.xtype= b.xusertype
	INNER JOIN sysobjects d ON a.id= d.id 
	AND d.xtype= 'U' 
	AND d.name<> 'dtproperties'
	LEFT JOIN sys.extended_properties g ON a.id= g.major_id 
	AND a.colid= g.minor_id
WHERE
	b.name IS NOT NULL 
	AND
	d.name= 'DayData_Gas' 
ORDER BY
	a.id,
	a.colorder

PostgreSql中查询的操作

查询当前数据库中所有表名

SELECT DISTINCT
	relname AS table_name,
	obj_description ( oid ) AS description 
FROM
	pg_class C 
WHERE
	relname IN ( SELECT DISTINCT tablename FROM pg_tables WHERE schemaname = "schemaName" AND POSITION ( '_2' IN tablename ) = 0 ) 
	AND relkind = 'r';

根据表名称查询所有字段和注释

SELECT
	A.attname AS column_name,
	col_description ( A.attrelid, A.attnum ) AS description

FROM
	pg_class AS C,
	pg_attribute AS A 
WHERE
	C.relname = 'data_source' 
	AND A.attrelid = C.oid 
	AND A.attnum >0

Oracle中查询的操作

查询用户的所有表和注释

select utc.table_name, utc.comments AS description from user_tab_comments utc;

根据表名称查询所有字段和注释

select column_name, comments AS description from user_col_comments WHERE table_name = 'TEST_CAR';