用T-SQL得到数据库的可视化结构 

 1 SELECT   
 2               表名=case   when   a.colorder=1   then   d.name   else   ' '   end, 
 3               字段序号=a.colorder, 
 4               字段名=a.name, 
 5               标识=case   when   COLUMNPROPERTY(   a.id,a.name, 'IsIdentity ')=1   then   '√ 'else   ' '   end, 
 6               主键=case   when   exists(SELECT   1   FROM   sysobjects   where   xtype= 'PK '   and   name   in   ( 
 7                             SELECT   name   FROM   sysindexes   WHERE   indid   in( 
 8                                           SELECT   indid   FROM   sysindexkeys   WHERE   id   =   a.id   AND   colid=a.colid 
 9                             )))   then   '√ '   else   ' '   end, 
10               类型=b.name, 
11               占用字节数=a.length, 
12               长度=COLUMNPROPERTY(a.id,a.name, 'PRECISION '), 
13               小数位数=isnull(COLUMNPROPERTY(a.id,a.name, 'Scale '),0), 
14               允许空=case   when   a.isnullable=1   then   '√ 'else   ' '   end, 
15               默认值=isnull(e.text, ' '), 
16               字段说明=isnull(g.[value], ' ') 
17 FROM   syscolumns   a 
18               left   join   systypes   b   on   a.xtype=b.xusertype 
19               inner   join   sysobjects   d   on   a.id=d.id     and   d.xtype= 'U '   and     d.name <> 'dtproperties ' 
20               left   join   syscomments   e   on   a.cdefault=e.id 
21               left   join   sysproperties   g   on   a.id=g.id   and   a.colid=g.smallid     
22 order   by   a.id,a.colorder
posted @ 2011-03-02 14:25  angtianqiang  阅读(284)  评论(0)    收藏  举报