Mysql与Sql Server查询数据库中表以及表字段

1.查询数据库表信息

　　mysql查询数据库中所有表信息

SELECT
    table_name AS '表名',
    table_comment AS '说明',
    create_time AS '创建时间',
    update_time AS '修改时间' 
FROM
    information_schema.TABLES

　　sql server查询数据库中所有表信息

SELECT
    so.name AS '表名',
    isnull(f.value,'')AS '说明',
    so.create_date AS '创建时间',
    so.modify_date AS '修改时间' 
FROM
    sys.objects so
    LEFT JOIN sys.extended_properties f ON f.major_id = so.object_id 
    AND f.minor_id= 0 
WHERE
    so.type = 'U';

 

2.查询表字段信息

　　mysql根据表名查询表字段信息

SELECT
    column_name as '字段名',
    data_type as '类型',
    column_comment '说明'
FROM
    information_schema.COLUMNS 
WHERE
    table_name = 'sys_user' and table_schema = (select database()) order by ordinal_position

　　sql server根据表名查询表字段信息

SELECT
    a.NAME as '字段名',
    b.NAME  as '类型',
    g.[VALUE] as '说明' 
FROM
    syscolumns a
    LEFT JOIN systypes b ON a.xusertype = b.xusertype
    LEFT JOIN sys.extended_properties g ON a.id = g.major_id 
    AND a.colid = g.minor_id
    INNER JOIN sysobjects d ON a.id = d.id 
    AND d.xtype = 'U' 
    AND d.NAME <> 'dtproperties' 
WHERE
    d.NAME = 'sys_dept'

 

注：参考文档