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'

 

转载于:https://www.cnblogs.com/lxh168/p/9049385.html