SQL Server将数据导出SQL脚本的方法代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】 SQL Server将数据导出SQL脚本的方法
作者:Dezai.CN / 发布于2011/5/11/ 685
使用方法为：
在查询分析器以“文本显示结果”方法执行
exec UspOutputData 你的表名
然后将运行后的结果存成.sql，加上用SQL Server生成的数据库脚本就可以了。
缺点和问题：
得到导出数据的语句，但image,text,ntext,sql_variant 列不出现在语句，以后改进。
<div>
<div id="cnblogs_post_body">
<strong>用这个存储过程可以实现：</strong>
<span style="color: #0000ff">CREATE</span><span style="color: #000000"> </span><span style="color: #0000ff">PROCEDURE</span><span style="color: #000000"> dbo.UspOutputData 
</span><span style="color: #008000">@tablename</span><span style="color: #000000"> sysname 
</span><span style="color: #0000ff">AS</span><span style="color: #000000"> 
</span><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@column</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">1000</span><span style="color: #000000">) 
</span><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@columndata</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">1000</span><span style="color: #000000">) 
</span><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@sql</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">varchar</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">4000</span><span style="color: #000000">) 
</span><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@xtype</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">tinyint</span><span style="color: #000000"> 
</span><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@name</span><span style="color: #000000"> sysname 
</span><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@objectId</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">int</span><span style="color: #000000"> 
</span><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@objectname</span><span style="color: #000000"> sysname 
</span><span style="color: #0000ff">declare</span><span style="color: #000000"> </span><span style="color: #008000">@ident</span><span style="color: #000000"> </span><span style="color: #000000; font-weight: bold">int</span><span style="color: #000000"> 

</span><span style="color: #0000ff">set</span><span style="color: #000000"> nocount </span><span style="color: #0000ff">on</span><span style="color: #000000"> 
</span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@objectId</span><span style="color: #808080">=</span><span style="color: #ff00ff">object_id</span><span style="color: #000000">(</span><span style="color: #008000">@tablename</span><span style="color: #000000">) 

</span><span style="color: #0000ff">if</span><span style="color: #000000"> </span><span style="color: #008000">@objectId</span><span style="color: #000000"> </span><span style="color: #0000ff">is</span><span style="color: #000000"> </span><span style="color: #0000ff">null</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080"> 判斷對象是否存在 </span><span style="color: #008080">
</span><span style="color: #0000ff">begin</span><span style="color: #000000"> 
</span><span style="color: #0000ff">print</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">The object not exists</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
</span><span style="color: #0000ff">return</span><span style="color: #000000"> 
</span><span style="color: #0000ff">end</span><span style="color: #000000"> 
</span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@objectname</span><span style="color: #808080">=</span><span style="color: #ff00ff">rtrim</span><span style="color: #000000">(</span><span style="color: #ff00ff">object_name</span><span style="color: #000000">(</span><span style="color: #008000">@objectId</span><span style="color: #000000">)) 

</span><span style="color: #0000ff">if</span><span style="color: #000000"> </span><span style="color: #008000">@objectname</span><span style="color: #000000"> </span><span style="color: #0000ff">is</span><span style="color: #000000"> </span><span style="color: #0000ff">null</span><span style="color: #000000"> </span><span style="color: #808080">or</span><span style="color: #000000"> </span><span style="color: #ff00ff">charindex</span><span style="color: #000000">(</span><span style="color: #008000">@objectname</span><span style="color: #000000">,</span><span style="color: #008000">@tablename</span><span style="color: #000000">)</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080">此判断不严密 </span><span style="color: #008080">
</span><span style="color: #0000ff">begin</span><span style="color: #000000"> 
</span><span style="color: #0000ff">print</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">object not in current database</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
</span><span style="color: #0000ff">return</span><span style="color: #000000"> 
</span><span style="color: #0000ff">end</span><span style="color: #000000"> 

</span><span style="color: #0000ff">if</span><span style="color: #000000"> </span><span style="color: #ff00ff">OBJECTPROPERTY</span><span style="color: #000000">(</span><span style="color: #008000">@objectId</span><span style="color: #000000">,</span><span style="color: #ff0000">'</span><span style="color: #ff0000">IsTable</span><span style="color: #ff0000">'</span><span style="color: #000000">) </span><span style="color: #808080"><</span><span style="color: #000000"> </span><span style="color: #808080">></span><span style="color: #000000"> </span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080"> 判斷對象是否是table </span><span style="color: #008080">
</span><span style="color: #0000ff">begin</span><span style="color: #000000"> 
</span><span style="color: #0000ff">print</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">The object is not table</span><span style="color: #ff0000">'</span><span style="color: #000000"> 
</span><span style="color: #0000ff">return</span><span style="color: #000000"> 
</span><span style="color: #0000ff">end</span><span style="color: #000000"> 

</span><span style="color: #0000ff">select</span><span style="color: #000000"> </span><span style="color: #008000">@ident</span><span style="color: #808080">=</span><span style="color: #000000">status</span><span style="color: #808080">&</span><span style="color: #800000; font-weight: bold">0x80</span><span style="color: #000000"> </span><span style="color: #0000ff">from</span><span style="color: #000000"> syscolumns </span><span style="color: #0000ff">where</span><span style="color: #000000"> id</span><span style="color: #808080">=</span><span style="color: #008000">@objectid</span><span style="color: #000000"> </span><span style="color: #808080">and</span><span style="color: #000000"> status</span><span style="color: #808080">&</span><span style="color: #800000; font-weight: bold">0x80</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0x80</span><span style="color: #000000"> 

</span><span style="color: #0000ff">if</span><span style="color: #000000"> </span><span style="color: #008000">@ident</span><span style="color: #000000"> </span><span style="color: #0000ff">is</span><span style="color: #000000"> </span><span style="color: #808080">not</span><span style="color: #000000"> </span><span style="color: #0000ff">null</span><span style="color: #000000"> 
</span><span style="color: #0000ff">print</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">SET IDENTITY_INSERT </span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #008000">@TableName</span><span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000"> ON</span><span style="color: #ff0000">'</span><span style="color: #000000"> 

</span><span style="color: #0000ff">declare</span><span style="color: #000000"> syscolumns_cursor </span><span style="color: #0000ff">cursor</span><span style="color: #000000"> 
</span><span style="color: #0000ff">for</span><span style="color: #000000"> </span><span style="color: #0000ff">select</span><span style="color: #000000"> c.name,c.xtype </span><span style="color: #0000ff">from</span><span style="color: #000000"> syscolumns c 
</span><span style="color: #0000ff">where</span><span style="color: #000000"> c.id</span><span style="color: #808080">=</span><span style="color: #008000">@objectid</span><span style="color: #000000"> 
</span><span style="color: #0000ff">order</span><span style="color: #000000"> </span><span style="color: #0000ff">by</span><span style="color: #000000"> c.colid 
</span><span style="color: #0000ff">open</span><span style="color: #000000"> syscolumns_cursor 
</span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@column</span><span style="color: #808080">=</span><span style="color: #ff0000">''</span><span style="color: #000000"> 
</span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@columndata</span><span style="color: #808080">=</span><span style="color: #ff0000">''</span><span style="color: #000000"> 
</span><span style="color: #0000ff">fetch</span><span style="color: #000000"> </span><span style="color: #0000ff">next</span><span style="color: #000000"> </span><span style="color: #0000ff">from</span><span style="color: #000000"> syscolumns_cursor </span><span style="color: #0000ff">into</span><span style="color: #000000"> </span><span style="color: #008000">@name</span><span style="color: #000000">,</span><span style="color: #008000">@xtype</span><span style="color: #000000"> 
</span><span style="color: #0000ff">while</span><span style="color: #000000"> </span><span style="color: #008000; font-weight: bold">@@fetch_status</span><span style="color: #000000"> </span><span style="color: #808080"><</span><span style="color: #000000"> </span><span style="color: #808080">>-</span><span style="color: #800000; font-weight: bold">1</span><span style="color: #000000"> 
</span><span style="color: #0000ff">begin</span><span style="color: #000000"> 
</span><span style="color: #0000ff">if</span><span style="color: #000000"> </span><span style="color: #008000; font-weight: bold">@@fetch_status</span><span style="color: #000000"> </span><span style="color: #808080"><</span><span style="color: #000000"> </span><span style="color: #808080">>-</span><span style="color: #800000; font-weight: bold">2</span><span style="color: #000000"> 
</span><span style="color: #0000ff">begin</span><span style="color: #000000"> 
</span><span style="color: #0000ff">if</span><span style="color: #000000"> </span><span style="color: #008000">@xtype</span><span style="color: #000000"> </span><span style="color: #808080">not</span><span style="color: #000000"> </span><span style="color: #808080">in</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">189</span><span style="color: #000000">,</span><span style="color: #800000; font-weight: bold">34</span><span style="color: #000000">,</span><span style="color: #800000; font-weight: bold">35</span><span style="color: #000000">,</span><span style="color: #800000; font-weight: bold">99</span><span style="color: #000000">,</span><span style="color: #800000; font-weight: bold">98</span><span style="color: #000000">) </span><span style="color: #008080">--</span><span style="color: #008080">timestamp不需处理，image,text,ntext,sql_variant 暂时不处理 </span><span style="color: #008080">
</span><span style="color: #0000ff">begin</span><span style="color: #000000"> 
</span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@column</span><span style="color: #808080">=</span><span style="color: #008000">@column</span><span style="color: #808080">+</span><span style="color: #ff00ff">case</span><span style="color: #000000"> </span><span style="color: #0000ff">when</span><span style="color: #000000"> </span><span style="color: #ff00ff">len</span><span style="color: #000000">(</span><span style="color: #008000">@column</span><span style="color: #000000">)</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000"> </span><span style="color: #0000ff">then</span><span style="color: #ff0000">''</span><span style="color: #000000"> </span><span style="color: #0000ff">else</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">,</span><span style="color: #ff0000">'</span><span style="color: #0000ff">end</span><span style="color: #808080">+</span><span style="color: #008000">@name</span><span style="color: #000000"> 
</span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@columndata</span><span style="color: #808080">=</span><span style="color: #008000">@columndata</span><span style="color: #808080">+</span><span style="color: #ff00ff">case</span><span style="color: #000000"> </span><span style="color: #0000ff">when</span><span style="color: #000000"> </span><span style="color: #ff00ff">len</span><span style="color: #000000">(</span><span style="color: #008000">@columndata</span><span style="color: #000000">)</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">0</span><span style="color: #000000"> </span><span style="color: #0000ff">then</span><span style="color: #000000"> </span><span style="color: #ff0000">''</span><span style="color: #000000"> </span><span style="color: #0000ff">else</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">,</span><span style="color: #ff0000">''</span><span style="color: #ff0000">,</span><span style="color: #ff0000">''</span><span style="color: #ff0000">,</span><span style="color: #ff0000">'</span><span style="color: #0000ff">end</span><span style="color: #000000"> 
</span><span style="color: #808080">+</span><span style="color: #ff00ff">case</span><span style="color: #000000"> </span><span style="color: #0000ff">when</span><span style="color: #000000"> </span><span style="color: #008000">@xtype</span><span style="color: #000000"> </span><span style="color: #808080">in</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">167</span><span style="color: #000000">,</span><span style="color: #800000; font-weight: bold">175</span><span style="color: #000000">) </span><span style="color: #0000ff">then</span><span style="color: #000000"> </span><span style="color: #ff0000">'''''''''</span><span style="color: #ff0000">+</span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #008000">@name</span><span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000">+</span><span style="color: #ff0000">'''''''''</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080">varchar,char </span><span style="color: #008080">
</span><span style="color: #0000ff">when</span><span style="color: #000000"> </span><span style="color: #008000">@xtype</span><span style="color: #000000"> </span><span style="color: #808080">in</span><span style="color: #000000">(</span><span style="color: #800000; font-weight: bold">231</span><span style="color: #000000">,</span><span style="color: #800000; font-weight: bold">239</span><span style="color: #000000">) </span><span style="color: #0000ff">then</span><span style="color: #000000"> </span><span style="color: #ff0000">'''</span><span style="color: #ff0000">N</span><span style="color: #ff0000">''''''</span><span style="color: #ff0000">+</span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #008000">@name</span><span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000">+</span><span style="color: #ff0000">'''''''''</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080">nvarchar,nchar </span><span style="color: #008080">
</span><span style="color: #0000ff">when</span><span style="color: #000000"> </span><span style="color: #008000">@xtype</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">61</span><span style="color: #000000"> </span><span style="color: #0000ff">then</span><span style="color: #000000"> </span><span style="color: #ff0000">'''''''''</span><span style="color: #ff0000">+convert(char(23),</span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #008000">@name</span><span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000">,121)+</span><span style="color: #ff0000">'''''''''</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080">datetime </span><span style="color: #008080">
</span><span style="color: #0000ff">when</span><span style="color: #000000"> </span><span style="color: #008000">@xtype</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">58</span><span style="color: #000000"> </span><span style="color: #0000ff">then</span><span style="color: #000000"> </span><span style="color: #ff0000">'''''''''</span><span style="color: #ff0000">+convert(char(16),</span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #008000">@name</span><span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000">,120)+</span><span style="color: #ff0000">'''''''''</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080">smalldatetime </span><span style="color: #008080">
</span><span style="color: #0000ff">when</span><span style="color: #000000"> </span><span style="color: #008000">@xtype</span><span style="color: #808080">=</span><span style="color: #800000; font-weight: bold">36</span><span style="color: #000000"> </span><span style="color: #0000ff">then</span><span style="color: #000000"> </span><span style="color: #ff0000">'''''''''</span><span style="color: #ff0000">+convert(char(36),</span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #008000">@name</span><span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000">)+</span><span style="color: #ff0000">'''''''''</span><span style="color: #000000"> </span><span style="color: #008080">--</span><span style="color: #008080">uniqueidentifier </span><span style="color: #008080">
</span><span style="color: #0000ff">else</span><span style="color: #000000"> </span><span style="color: #008000">@name</span><span style="color: #000000"> </span><span style="color: #0000ff">end</span><span style="color: #000000"> 
</span><span style="color: #0000ff">end</span><span style="color: #000000"> 
</span><span style="color: #0000ff">end</span><span style="color: #000000"> 
</span><span style="color: #0000ff">fetch</span><span style="color: #000000"> </span><span style="color: #0000ff">next</span><span style="color: #000000"> </span><span style="color: #0000ff">from</span><span style="color: #000000"> syscolumns_cursor </span><span style="color: #0000ff">into</span><span style="color: #000000"> </span><span style="color: #008000">@name</span><span style="color: #000000">,</span><span style="color: #008000">@xtype</span><span style="color: #000000"> 
</span><span style="color: #0000ff">end</span><span style="color: #000000"> 
</span><span style="color: #0000ff">close</span><span style="color: #000000"> syscolumns_cursor 
</span><span style="color: #0000ff">deallocate</span><span style="color: #000000"> syscolumns_cursor 

</span><span style="color: #0000ff">set</span><span style="color: #000000"> </span><span style="color: #008000">@sql</span><span style="color: #808080">=</span><span style="color: #ff0000">'</span><span style="color: #ff0000">set nocount on select </span><span style="color: #ff0000">''</span><span style="color: #ff0000">insert </span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #008000">@tablename</span><span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000">(</span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #008000">@column</span><span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000">) values(</span><span style="color: #ff0000">''</span><span style="color: #ff0000">as </span><span style="color: #ff0000">''</span><span style="color: #ff0000">--</span><span style="color: #ff0000">''</span><span style="color: #ff0000">,</span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #008000">@columndata</span><span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000">,</span><span style="color: #ff0000">''</span><span style="color: #ff0000">)</span><span style="color: #ff0000">''</span><span style="color: #ff0000"> from </span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #008000">@tablename</span><span style="color: #000000"> 

</span><span style="color: #0000ff">print</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">--</span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #008000">@sql</span><span style="color: #000000"> 
</span><span style="color: #0000ff">exec</span><span style="color: #000000">(</span><span style="color: #008000">@sql</span><span style="color: #000000">) 

</span><span style="color: #0000ff">if</span><span style="color: #000000"> </span><span style="color: #008000">@ident</span><span style="color: #000000"> </span><span style="color: #0000ff">is</span><span style="color: #000000"> </span><span style="color: #808080">not</span><span style="color: #000000"> </span><span style="color: #0000ff">null</span><span style="color: #000000"> 
</span><span style="color: #0000ff">print</span><span style="color: #000000"> </span><span style="color: #ff0000">'</span><span style="color: #ff0000">SET IDENTITY_INSERT </span><span style="color: #ff0000">'</span><span style="color: #808080">+</span><span style="color: #008000">@TableName</span><span style="color: #808080">+</span><span style="color: #ff0000">'</span><span style="color: #ff0000"> OFF</span><span style="color: #ff0000">'</span><span style="color: #000000"> 

</span><span style="color: #0000ff">GO</span>
</div>
<script type="text/javascript">
if ($ != jQuery) {
	$ = jQuery.noConflict();
}
var isLogined = false;
var cb_blogId = 66811;
var cb_entryId = 1841312;
var cb_blogApp = "cwfsoft";
var cb_blogUserGuid = "a66c8b66-7617-df11-ba8f-001cf0cd104b";
var cb_entryCreatedDate = '2010/10/2 14:54:00';
</script>
</div>
试试其它关键字
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)