常用数据库获取所有表结构信息

MYSQL:

1、数据库中所有表的信息
SELECT * FROM information_schema.TABLES WHERE TABLE_SCHEMA = '数据库名'
2、数据库中每个表的数据量
SELECT table_name,table_rows FROM information_schema.tables
WHERE TABLE_SCHEMA = '数据库名' ORDER BY table_rows DESC;

GREENPLUM:
查询所有库下的表结构

SELECT A.SCHEMANAME,
A.TABLENAME,
D.ATTNAME,
REPLACE(REPLACE(REPLACE(FORMAT_TYPE(D.ATTTYPID, D.ATTTYPMOD),
'numeric',
'NUMBER'),
'character varying',
'VARCHAR2'),
'date',
'DATE') AS DATATYPE,
C.DESCRIPTION
FROM PG_TABLES A
INNER JOIN PG_CLASS B
ON A.TABLENAME = B.RELNAME
LEFT JOIN PG_CATALOG.PG_DESCRIPTION C
ON B.OID = C.OBJOID
LEFT JOIN PG_CATALOG.PG_ATTRIBUTE D
ON D.ATTRELID = C.OBJOID
AND D.ATTNUM = C.OBJSUBID

查询指定表名

SELECT
A.attnum,
( SELECT description FROM pg_catalog.pg_description WHERE objoid = A.attrelid AND objsubid = A.attnum ) AS descript,
A.attname,
( select typname from pg_type where oid = A.atttypid) AS type,
A.atttypmod AS data_type
FROM
pg_catalog.pg_attribute A
WHERE
1 = 1
AND A.attrelid = ( SELECT oid FROM pg_class WHERE relname = 'app_ar_dict_info' )
AND A.attnum > 0
AND NOT A.attisdropped
ORDER BY
A.attnum;

ORACLE:

　　1.查询数据库中所有表名称：

　　select t.table_name from user_tables t;

　　2.查询每张表中所有字段名：

　　SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME = '表名称';