不同数据库查看表结构

 

查看不同数据库：oracle,mysql,sqlserver,db2中表结构

oracle

select
　　t.table_name as "tableName",
　　t.COLUMN_NAME as "columnName",
　　t.DATA_TYPE as "dataType",
　　t.DATA_LENGTH as "dataLength",
　　t.DATA_PRECISION as "dataPrecision",
　　t.DATA_SCALE as "dataScale"
from  sys.dba_tab_columns t 
where t.table_name = #{tableName};           

 

其中tableName：表名，columnName：字段名，dataType：字段类型，dataLength：长度，dataPrecision：整数位长度，dataScale：小数位长度。下同

oracle中还可以使用user_tab_columns，它查询当前用户的表结构。

mysql

select 
    t.Table_name "tableName",
    t.COLUMN_NAME "columnName",
    t.DATA_TYPE "dataType",
    t.character_maximum_length "dataLength" ,
    t.numeric_precision "dataPercision",
    t.numeric_scale "dataScale"
from  information_schema.COLUMNS t
where t.table_name=#{tableName};

sqlserver

SELECT 
    (case when a.colorder=1 then d.name else null end) "tableName",
    a.name  "columnName",
    b.name "dataType",
    COLUMNPROPERTY(a.id,a.name,'PRECISION')  "dataLength",
    isnull(COLUMNPROPERTY(a.id,a.name,'Percision'),0)  "dataPercision",
    isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)  "dataScale"
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'
where b.name is not null"
    d.name=#{tableName}
order by a.id,a.colorder;

db2

select t.Table_name "tableName", 
    t.COLUMN_NAME "columnName",
    t.DATA_TYPE "dataType",
    t.character_maximum_length "dataLength",
    t.numeric_precision "dataPercision",
    t.numeric_scale "dataScale"
from  sysibm.columns t
where t.table_name=#{tableName};