postgresql,oracle,mysql通过数据字典获取表结构

postgresql,oracle,mysql通过数据字典获取表结构，需要schema名跟表名。

postgresql:
SELECT a.attname,pg_catalog.format_type(a.atttypid,a.atttypmod) AS data_type
FROM pg_catalog.pg_attribute a,
(SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
WHERE (c.relname) =lower('cxf')
AND (n.nspname) = lower('public')) b
WHERE a.attrelid = b.oid
AND a.attnum > 0
AND NOT a.attisdropped ORDER BY a.attnum

attname | data_type
---------+------------------------
a | integer
b | character varying(100)
(2 rows)

mysql:
create table dwarch.cxf(a int,b varchar(100));
SELECT COLUMN_NAME,CONCAT(DATA_TYPE,CASE WHEN UPPER(DATA_TYPE) LIKE '%CHAR%' THEN CONCAT ('(',CHARACTER_MAXIMUM_LENGTH,')') ELSE '' END ) AS DATATYPE
FROM information_schema.COLUMNS
WHERE UPPER(TABLE_SCHEMA)=UPPER('test')
AND UPPER(TABLE_NAME)=UPPER('cxf')

+-------------+--------------+
| COLUMN_NAME | DATATYPE |
+-------------+--------------+
| a | int |
| b | varchar(100) |
+-------------+--------------+

oracle:SELECT column_name,data_type||CASE WHEN data_type LIKE '%CHAR%' THEN '('||data_length||')' END as data_type FROM all_tab_columns WHERE owner=upper('test') and table_name=upper('cxf') ORDER BY column_id;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!