MySql与Sql Server显示表结构回顾
1. MySql显示表结构或者查看表的创建结构,示例(示例表'tb001')如下:
desc tb001 | show create table tb001
2. Sql server 显示表结构及相关信息
2.1. 显示表(示例表'tb001')的结构信息
SELECT syscolumns.name,systypes.name,syscolumns.isnullable, syscolumns.length FROM syscolumns,systypes WHERE syscolumns.xusertype = systypes.xusertype AND syscolumns.id = OBJECT_ID('tb001')
2.2. 数据库中所有表的id,name(字段名),name(类型名),length(类型长度)统计(根据id作升序排序):
SELECT syscolumns.id,syscolumns.name,systypes.name,syscolumns.isnullable, syscolumns.length FROM syscolumns,systypes WHERE syscolumns.xusertype = systypes.xusertype ORDER BY syscolumns.id
2.3. 查看数据库中所有表对应的列数:
SELECT syscolumns.id,COUNT(*) as columns_number FROM syscolumns,systypes WHERE syscolumns.xusertype = systypes.xusertype GROUP BY syscolumns.id ORDER BY COUNT(*) DESC
2.4. 根据id查看id对应表的创建信息等:
SELECT * FROM sysobjects WHERE id='8266051'