[Database] Data Dictionary的查询方法

博客首页 » Database Data Dictionary的查询方法

发布于 09 Jun 2014 01:09
标签 blog
postgresql,oracle,mysql通过数据字典获取表结构,需要schema名跟表名。

http://www.alberton.info/postgresql_meta_info.html#.Umaq5dHEftM

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)

简化版

select * from information_schema.columns where table_catalog = 'TestDB' and table_name in ('hd_district_mst') and table_schema not in ('pg_catalog','information_schema') limit 100
select * from information_schema.tables where table_catalog = 'TestDB' and table_schema not in ('pg_catalog','information_schema') limit 100

SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod) AS data_type
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_attribute a
ON a.attrelid = c.oid
AND a.attnum > 0
AND NOT a.attisdropped
WHERE c.relname =lower('hd_district_mst')
ORDER BY a.attnum

带注释版
SELECT
(select n.nspname AS schema_name from pg_namespace n where n.oid = c.relnamespace) schema_name,
c.relname AS table_name,
(select d.description from pg_catalog.pg_description d where c.oid = d.objoid and objsubid = 0) table_comment,
a.attnum AS ordinal_position,
a.attname AS column_name,
(select t.typname AS data_type from pg_type t where a.atttypid = t.oid) column_type,
a.attlen AS character_maximum_length,
(select d.description from pg_catalog.pg_description d where c.oid = d.objoid and d.objsubid = a.attnum) column_comment,
a.atttypmod AS modifier,
a.attnotnull AS notnull,
a.atthasdef AS hasdefault
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid
WHERE c.relname = 'the_table_name'
AND a.attnum > 0
ORDER BY a.attnum

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;


本页面的文字允许在知识共享 署名-相同方式共享 3.0协议和GNU自由文档许可证下修改和再使用,仅有一个特殊要求,请用链接方式注明文章引用出处及作者。请协助维护作者合法权益。


系列文章

文章列表

  • Database Data Dictionary的查询方法

这篇文章对你有帮助吗,投个票吧?

rating: 0+x

留下你的评论

Add a New Comment
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License