SQL Server里面如何导出包含(insert into)数据的SQL脚本
<DIV>通常情况下，SQL Server里面的生成SQL脚本，只会包含数据库及表的字段结构，而不会包含表的数据，也就是SQL脚本里面只有Create database，Create table 这样的语句，没有insert into。<P>因为SQL Server并不包含这个功能，只能靠第三方的代码了。</P>
<P>以下存储过程可以实现：</P>
<DIV style="PADDING-BOTTOM: 4px; PADDING-LEFT: 5.4pt; WIDTH: 95%; PADDING-RIGHT: 5.4pt; PADDING-TOP: 4px">
<DIV><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><SPAN style="COLOR: #0000ff">CREATE</SPAN> <SPAN style="COLOR: #0000ff">PROCEDURE</SPAN> <SPAN style="COLOR: #000000">dbo.UspOutputData <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #008000">@tablename</SPAN> <SPAN style="COLOR: #000000">sysname <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">AS</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">declare</SPAN> <SPAN style="COLOR: #008000">@column</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">) <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">declare</SPAN> <SPAN style="COLOR: #008000">@columndata</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">) <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">declare</SPAN> <SPAN style="COLOR: #008000">@sql</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">) <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">declare</SPAN> <SPAN style="COLOR: #008000">@xtype</SPAN> <SPAN style="COLOR: #000000; FONT-WEIGHT: bold">tinyint</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">declare</SPAN> <SPAN style="COLOR: #008000">@name</SPAN> <SPAN style="COLOR: #000000">sysname <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">declare</SPAN> <SPAN style="COLOR: #008000">@objectId</SPAN> <SPAN style="COLOR: #000000; FONT-WEIGHT: bold">int</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">declare</SPAN> <SPAN style="COLOR: #008000">@objectname</SPAN> <SPAN style="COLOR: #000000">sysname <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">declare</SPAN> <SPAN style="COLOR: #008000">@ident</SPAN> <SPAN style="COLOR: #000000; FONT-WEIGHT: bold">int</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">set</SPAN> <SPAN style="COLOR: #000000">nocount </SPAN><SPAN style="COLOR: #0000ff">on</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">set</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">) <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">if</SPAN> <SPAN style="COLOR: #008000">@objectId</SPAN> <SPAN style="COLOR: #0000ff">is</SPAN> <SPAN style="COLOR: #0000ff">null</SPAN> <SPAN style="COLOR: #008080">--</SPAN> <SPAN style="COLOR: #008080">判断对象是否存在 </SPAN><SPAN style="COLOR: #008080"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">begin</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">print</SPAN> <SPAN style="COLOR: #ff0000">'</SPAN> <SPAN style="COLOR: #ff0000">The object not exists</SPAN> <SPAN style="COLOR: #ff0000">'</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">return</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">end</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">set</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">)) <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">if</SPAN> <SPAN style="COLOR: #008000">@objectname</SPAN> <SPAN style="COLOR: #0000ff">is</SPAN> <SPAN style="COLOR: #0000ff">null</SPAN> <SPAN style="COLOR: #808080">or</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: #008080">--</SPAN> <SPAN style="COLOR: #008080">此判断不严密 </SPAN><SPAN style="COLOR: #008080"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">begin</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">print</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"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">return</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">end</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">if</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: #808080">></SPAN> <SPAN style="COLOR: #800000; FONT-WEIGHT: bold">1</SPAN> <SPAN style="COLOR: #008080">--</SPAN> <SPAN style="COLOR: #008080">判断对象是否是table </SPAN><SPAN style="COLOR: #008080"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">begin</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">print</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"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">return</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">end</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">select</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: #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: #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"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">if</SPAN> <SPAN style="COLOR: #008000">@ident</SPAN> <SPAN style="COLOR: #0000ff">is</SPAN> <SPAN style="COLOR: #808080">not</SPAN> <SPAN style="COLOR: #0000ff">null</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">print</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"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">declare</SPAN> <SPAN style="COLOR: #000000">syscolumns_cursor </SPAN><SPAN style="COLOR: #0000ff">cursor</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">for</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: #0000ff">order</SPAN> <SPAN style="COLOR: #0000ff">by</SPAN> <SPAN style="COLOR: #000000">c.colid <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">open</SPAN> <SPAN style="COLOR: #000000">syscolumns_cursor <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">set</SPAN> <SPAN style="COLOR: #008000">@column</SPAN> <SPAN style="COLOR: #808080">=</SPAN> <SPAN style="COLOR: #ff0000">''</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">set</SPAN> <SPAN style="COLOR: #008000">@columndata</SPAN> <SPAN style="COLOR: #808080">=</SPAN> <SPAN style="COLOR: #ff0000">''</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">fetch</SPAN> <SPAN style="COLOR: #0000ff">next</SPAN> <SPAN style="COLOR: #0000ff">from</SPAN> <SPAN style="COLOR: #000000">syscolumns_cursor </SPAN><SPAN style="COLOR: #0000ff">into</SPAN> <SPAN style="COLOR: #008000">@name</SPAN> <SPAN style="COLOR: #000000">,</SPAN> <SPAN style="COLOR: #008000">@xtype</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">while</SPAN> <SPAN style="COLOR: #008000; FONT-WEIGHT: bold">@@fetch_status</SPAN> <SPAN style="COLOR: #808080"><</SPAN> <SPAN style="COLOR: #808080">>-</SPAN> <SPAN style="COLOR: #800000; FONT-WEIGHT: bold">1</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">begin</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">if</SPAN> <SPAN style="COLOR: #008000; FONT-WEIGHT: bold">@@fetch_status</SPAN> <SPAN style="COLOR: #808080"><</SPAN> <SPAN style="COLOR: #808080">>-</SPAN> <SPAN style="COLOR: #800000; FONT-WEIGHT: bold">2</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">begin</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">if</SPAN> <SPAN style="COLOR: #008000">@xtype</SPAN> <SPAN style="COLOR: #808080">not</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"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">begin</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">set</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: #0000ff">when</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: #0000ff">then</SPAN> <SPAN style="COLOR: #ff0000">''</SPAN> <SPAN style="COLOR: #0000ff">else</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"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">set</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: #0000ff">when</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: #0000ff">then</SPAN> <SPAN style="COLOR: #ff0000">''</SPAN> <SPAN style="COLOR: #0000ff">else</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: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">end</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #808080">+</SPAN> <SPAN style="COLOR: #ff00ff">case</SPAN> <SPAN style="COLOR: #0000ff">when</SPAN> <SPAN style="COLOR: #008000">@xtype</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: #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: #008080">--</SPAN> <SPAN style="COLOR: #008080">varchar,char </SPAN><SPAN style="COLOR: #008080"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">when</SPAN> <SPAN style="COLOR: #008000">@xtype</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: #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: #008080">--</SPAN> <SPAN style="COLOR: #008080">nvarchar,nchar </SPAN><SPAN style="COLOR: #008080"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">when</SPAN> <SPAN style="COLOR: #008000">@xtype</SPAN> <SPAN style="COLOR: #808080">=</SPAN> <SPAN style="COLOR: #800000; FONT-WEIGHT: bold">61</SPAN> <SPAN style="COLOR: #0000ff">then</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: #008080">--</SPAN> <SPAN style="COLOR: #008080">datetime </SPAN><SPAN style="COLOR: #008080"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">when</SPAN> <SPAN style="COLOR: #008000">@xtype</SPAN> <SPAN style="COLOR: #808080">=</SPAN> <SPAN style="COLOR: #800000; FONT-WEIGHT: bold">58</SPAN> <SPAN style="COLOR: #0000ff">then</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: #008080">--</SPAN> <SPAN style="COLOR: #008080">smalldatetime </SPAN><SPAN style="COLOR: #008080"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">when</SPAN> <SPAN style="COLOR: #008000">@xtype</SPAN> <SPAN style="COLOR: #808080">=</SPAN> <SPAN style="COLOR: #800000; FONT-WEIGHT: bold">36</SPAN> <SPAN style="COLOR: #0000ff">then</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: #008080">--</SPAN> <SPAN style="COLOR: #008080">uniqueidentifier </SPAN><SPAN style="COLOR: #008080"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">else</SPAN> <SPAN style="COLOR: #008000">@name</SPAN> <SPAN style="COLOR: #0000ff">end</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">end</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">end</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">fetch</SPAN> <SPAN style="COLOR: #0000ff">next</SPAN> <SPAN style="COLOR: #0000ff">from</SPAN> <SPAN style="COLOR: #000000">syscolumns_cursor </SPAN><SPAN style="COLOR: #0000ff">into</SPAN> <SPAN style="COLOR: #008000">@name</SPAN> <SPAN style="COLOR: #000000">,</SPAN> <SPAN style="COLOR: #008000">@xtype</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">end</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">close</SPAN> <SPAN style="COLOR: #000000">syscolumns_cursor <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">deallocate</SPAN> <SPAN style="COLOR: #000000">syscolumns_cursor <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">set</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"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">print</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"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">exec</SPAN> <SPAN style="COLOR: #000000">(</SPAN> <SPAN style="COLOR: #008000">@sql</SPAN> <SPAN style="COLOR: #000000">) <BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">if</SPAN> <SPAN style="COLOR: #008000">@ident</SPAN> <SPAN style="COLOR: #0000ff">is</SPAN> <SPAN style="COLOR: #808080">not</SPAN> <SPAN style="COLOR: #0000ff">null</SPAN> <SPAN style="COLOR: #000000"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">print</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"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"><BR><IMG alt="" align=top src="http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif"></SPAN> <SPAN style="COLOR: #0000ff">GO</SPAN></DIV></DIV>
<P>使用方法： </P>
<P>exec UspOutputData 你的表名 </P>
<P>选择【执行模式】为“以文本显示结果”，然后将运行后的结果存成.sql，加上用SQL Server生成的数据库脚本就可以了。</P>
<P>另外可以利用第三方工具，导出数据可以用powerbuilder。在database painter里面，用SQL选出，或者直接打开表，点击生成的list datawindow,然后在菜单file->save rows as->选择SQL，那么生成的SQL语句就包括建表和insert数据的SQL了。</P></DIV>
页:
[1]