使用sql获取各数据库(MySQL、PostgreSQL、Oracle、MsSQL)的表结构

迷失缠绵情愫中

通常我们提到数据库表结构的时候，比较关心的几个属性：字段名称、类型、长度、是否主键、是否自增、是否为空、默认值、备注。
那么，使用SQL语句获取这些属性，在各主流数据库下是什么样的呢？

MySQL数据库
作为时下最流行的数据库，MySQL的获取SQL是最简单的。
一般我们用下面一行语句就能搞定：

1. SHOW FULL COLUMNS FROM xxx;

复制代码

当然，也可以用MySQL自带的数据库information_schema中的表，例如：columns等查询更全的信息。


PostgreSQL数据库
使用其两个最重要的Schema(information_schema、pg_catalog)下表，进行关联查询，获取常用属性。

1. SELECT d.column_name AS "Field", d.udt_name AS "Type", COALESCE(d.character_maximum_length, d.numeric_precision, d.datetime_precision) AS "Length",
   
2.   CASE WHEN t.conname IS NOT NULL THEN 'PRI' ELSE '' END AS "Key",
   
3.   CASE WHEN s.extra IS NOT NULL THEN 'auto_increment' ELSE '' END "Extra",
   
4.   d.is_nullable AS "Null", f.adsrc AS "Default", col_description(a.attrelid, a.attnum) AS "Comment"
   
5. FROM information_schema.columns d, pg_class c, pg_attribute a
   
6. LEFT JOIN pg_constraint t ON (a.attrelid = t.conrelid AND t.contype = 'p' AND a.attnum = t.conkey[1])
   
7. LEFT JOIN pg_attrdef f ON (a.attrelid = f.adrelid AND a.attnum = f.adnum)
   
8. LEFT JOIN (SELECT 'nextval(''' || c.relname || '''::regclass)' AS extra FROM pg_class c WHERE c.relkind = 'S') s ON f.adsrc = s.extra
   
9. WHERE a.attrelid = c.oid
   
10. AND a.attnum > 0
    
11. AND c.relname = d.table_name
    
12. AND d.column_name = a.attname
    
13. AND c.relname = 'xxx'
    
14. ORDER BY a.attnum;

复制代码

Oracle数据库
系统表：user_col_comments能提供大部分信息，只有：备注(Comment)、主键(Key)需要关联其他表进行获取。

1. SELECT t.column_name AS "Field", t.data_type AS "Type", t.data_length AS "Length",
   
2.     CASE WHEN k.column_name IS NOT NULL THEN 'PRI' ELSE '' END AS "Key",
   
3.     CASE WHEN t.identity_column = 'YES' THEN 'auto_increment' ELSE '' END AS "Extra",
   
4.     CASE WHEN t.nullable = 'N' THEN 'NO' ELSE 'YES' END AS "Null",
   
5.     t.data_default AS "Default", c.comments AS "Comment"
   
6. FROM user_col_comments c, user_tab_cols t
   
7. LEFT JOIN (
   
8.     SELECT u.table_name, c.column_name
   
9.      FROM user_constraints u, user_cons_columns c
   
10.     WHERE u.table_name = c.table_name
    
11.     AND u.constraint_name = c.constraint_name
    
12.     AND u.constraint_type = 'P') k ON t.table_name = k.table_name AND t.column_name = k.column_name
    
13. WHERE t.table_name = c.table_name
    
14. AND t.column_name = c.column_name
    
15. AND t.table_name = 'xxx'
    
16. ORDER BY t.column_id;

复制代码

MsSQL数据库
MsSQL即SQL Server数据库。MsSQL相对更加复杂一些，关联的系统表也最多。

1. SELECT a.name AS 'Field', b.name AS 'Type', COLUMNPROPERTY(a.id,a.name,'PRECISION') AS 'Length',
   
2.   CASE WHEN p.name IS NOT NULL THEN 'PRI' ELSE '' END 'Key',
   
3.   CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN 'auto_increment' ELSE '' END AS 'Extra',
   
4.   CASE WHEN a.isnullable=1 THEN 'YES' ELSE 'NO' END AS 'Null',
   
5.   e.text AS 'Default', g.[value] AS 'Comment'
   
6. FROM sysobjects d, syscolumns a
   
7. LEFT JOIN systypes b ON a.xusertype = b.xusertype
   
8. LEFT JOIN syscomments e ON a.cdefault = e.id
   
9. LEFT JOIN sys.extended_properties g ON (a.id = G.major_id AND a.colid = g.minor_id)
   
10. LEFT JOIN (
    
11. SELECT s.name, k.id, k.colid FROM sysindexkeys k, sysindexes i, sysobjects s
    
12. WHERE k.indid = i.indid
    
13. AND s.name = i.name
    
14. AND s.xtype = 'PK'
    
15. ) p ON (p.id = a.id AND p.colid = a.colid)
    
16. WHERE d.id = a.id
    
17. AND d.xtype = 'U'
    
18. AND d.name = 'xxx'
    
19. ORDER BY a.colorder;

复制代码

注：以上均参考MySQL的属性名输出。xxx为需要查询的表名。