收藏几段语句和存储过程

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

--======================================================



--列出SQLSERVER所有表，字段名，主键，类型，长度，小数位数等信息



--在查询分析器里运行即可,可以生成一个表，导出到EXCEL中



--======================================================



SELECT

      (casewhena.colorder=1thend.nameelse''end)表名,

      a.colorder字段序号,

      a.name字段名,

      (casewhenCOLUMNPROPERTY(a.id,a.name,'IsIdentity')=1then'√'else''end)标识,

      (casewhen(SELECTcount(*)

      FROMsysobjects

      WHERE(namein

               (SELECTname

              FROMsysindexes

              WHERE(id=a.id)AND(indidin

                        (SELECTindid

                       FROMsysindexkeys

                       WHERE(id=a.id)AND(colidin

                                 (SELECTcolid

                                FROMsyscolumns

                                WHERE(id=a.id)AND(name=a.name)))))))AND

            (xtype='PK'))>0then'√'else''end)主键,

      b.name类型,

      a.length占用字节数,

      COLUMNPROPERTY(a.id,a.name,'PRECISION')as长度,

      isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0)as小数位数,

      (casewhena.isnullable=1then'√'else''end)允许空,

      isnull(e.text,'')默认值,

      isnull(g.[value],'')AS字段说明   


 


FROM syscolumns aleftjoinsystypesb

on a.xtype=b.xusertype

innerjoinsysobjectsd

ona.id=d.id and d.xtype='U'and d.name<>'dtproperties'共6页  第1页     
<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>