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

1.查询数据库表信息

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

SELECTtable_name AS '表名',table_comment AS '说明',create_time AS '创建时间',update_time AS '修改时间' FROMinformation_schema.TABLES

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

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

 

2.查询表字段信息

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

SELECTcolumn_name as '字段名',data_type as '类型',column_comment '说明' FROMinformation_schema.COLUMNS WHEREtable_name = 'sys_user' and table_schema = (select database()) order by ordinal_position

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

SELECTa.NAME as '字段名',b.NAME as '类型',g.[VALUE] as '说明' FROMsyscolumns aLEFT JOIN systypes b ON a.xusertype = b.xusertypeLEFT JOIN sys.extended_properties g ON a.id = g.major_id AND a.colid = g.minor_idINNER JOIN sysobjects d ON a.id = d.id AND d.xtype = 'U' AND d.NAME <> 'dtproperties' WHEREd.NAME = 'sys_dept'

 

注：参考文档

转载于:https://www.cnblogs.com/cglmj/p/10943715.html

总结

以上是生活随笔为你收集整理的Mysql与Sql Server查询数据库中表以及表字段的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。