显示中的表结构(新增了索引及表的描述信息)

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
SELECT
 表名=casewhena.colorder=1thend.nameelse''end,
 表说明=casewhena.colorder=1thenisnull(f.value,'')else''end,
 字段序号=a.colorder,
 字段名=a.name,
 标识=casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''end,
 主键=casewhenexists(SELECT1FROMsysobjectswherextype='PK'andnamein(
  SELECTnameFROMsysindexesWHEREindidin(
   SELECTindidFROMsysindexkeysWHEREid=a.idANDcolid=a.colid
  )))then'√'else''end,
 类型=b.name,
 占用字节数=a.length,
 长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
 小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
 允许空=casewhena.isnullable=1then'√'else''end,
 默认值=isnull(e.text,''),
 字段说明=isnull(g.[value],''),
 索引名称=isnull(h.索引名称,''),
 索引顺序=isnull(h.排序,'')
FROMsyscolumnsa
 leftjoinsystypesbona.xtype=b.xusertype
 innerjoinsysobjectsdona.id=d.id andd.xtype='U'and d.name<>'dtproperties'
 leftjoinsyscommentseona.cdefault=e.id
 leftjoinsyspropertiesgona.id=g.idanda.colid=g.smallid 
 leftjoinsyspropertiesfond.id=f.idandf.smallid=0
 leftjoin(--这部分是索引信息,如果要显示索引与表及字段的对应关系,可以只要此部分
  select索引名称=a.name,c.id,d.colid
   ,排序=caseindexkey_property(c.id,b.indid,b.keyno,'isdescending')
    when1then'降序'when0then'升序'end
  fromsysindexesa
   joinsysindexkeysbona.id=b.idanda.indid=b.indid
   join(--这里的作用是有多个索引时,取索引号最小的那个
    selectid,colid,indid=min(indid)fromsysindexkeys
    groupbyid,colid)b1onb.id=b1.idandb.colid=b1.colidandb.indid=b1.indid
   joinsysobjectsconb.id=c.idandc.xtype='U'and c.name<>'dtproperties'
   joinsyscolumnsdonb.id=d.idandb.colid=d.colid
  wherea.indidnotin(0,255)
 )hona.id=h.idanda.colid=h.colid
--whered.name='要查询的表'   --如果只查询指定表,加上此条件
orderbya.id,a.colorder

<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 728x15, 创建于 08-4-23MSDN */ google_ad_slot = "3624277373"; google_ad_width = 728; google_ad_height = 15; //--> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
<script type="text/javascript"><!-- google_ad_client = "pub-2947489232296736"; /* 160x600, 创建于 08-4-23MSDN */ google_ad_slot = "4367022601"; google_ad_width = 160; google_ad_height = 600; //--> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>
阅读更多
个人分类: 数据库
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭