有用的SQLServer语句和存储过程

<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>
SQL Server语句存储过程



 


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



--列出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"><!-- 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>
阅读更多
文章标签: sqlserver 存储 excel
个人分类: 数据库
想对作者说点什么? 我来说一句

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

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭