资源描述：
《SQL Server 2005导出脚本存储过程.docx》由会员上传分享，免费在线阅读，更多相关内容在教育资源-天天文库。
1、SQLServer2005导出包含(insertinto)数据的SQL脚本(使用存储过程)通常情况下，SQLServer里面的生成SQL脚本，只会包含数据库及表的字段结构，而不会包含表的数据，也就是SQL脚本里面只有Createdatabase，Createtable这样的语句，没有insertinto。因为SQLServer并不包含这个功能，只能靠第三方的代码了。以下存储过程可以实现：CREATEPROCEDUREdbo.UspOutputData@tablenamesysnameASdeclare@columnvarchar(1000)declar
2、e@columndatavarchar(1000)declare@sqlvarchar(4000)declare@xtypetinyintdeclare@namesysnamedeclare@objectIdintdeclare@objectnamesysnamedeclare@identintsetnocountonset@objectId=object_id(@tablename)if@objectIdisnull--判断对象是否存在beginprint'Theobjectnotexists'returnendset@objectname=rtri
3、m(object_name(@objectId))if@objectnameisnullorcharindex(@objectname,@tablename)=0--此判断不严密beginprint'objectnotincurrentdatabase'returnendifOBJECTPROPERTY(@objectId,'IsTable')<>1--判断对象是否是tablebeginprint'Theobjectisnottable'returnendselect@ident=status&0x80fromsyscolumnswhereid=@ob
4、jectidandstatus&0x80=0x80if@identisnotnullprint'SETIDENTITY_INSERT'+@TableName+'ON'declaresyscolumns_cursorcursorforselectc.name,c.xtypefromsyscolumnscwherec.id=@objectidorderbyc.colidopensyscolumns_cursorset@column=''set@columndata=''fetchnextfromsyscolumns_cursorinto@name,@xty
5、pewhile@@fetch_status<>-1beginif@@fetch_status<>-2beginif@xtypenotin(189,34,35,99,98)--timestamp不需处理，image,text,ntext,sql_variant暂时不处理beginset@column=@column+casewhenlen(@column)=0then''else','end+@nameset@columndata=@columndata+casewhenlen(@columndata)=0then''else','','','end+c
6、asewhen@xtypein(167,175)then'''''''''+'+@name+'+'''''''''--varchar,charwhen@xtypein(231,239)then'''N''''''+'+@name+'+'''''''''--nvarchar,ncharwhen@xtype=61then'''''''''+convert(char(23),'+@name+',121)+'''''''''--datetimewhen@xtype=58then'''''''''+convert(char(16),'+@name+',120)+
7、'''''''''--smalldatetimewhen@xtype=36then'''''''''+convert(char(36),'+@name+')+'''''''''--uniqueidentifierelse@nameendendendfetchnextfromsyscolumns_cursorinto@name,@xtypeendclosesyscolumns_cursordeallocatesyscolumns_cursorset@sql='setnocountonselect''insert'+@tablename+'('+@colu
8、mn+')values(''as''--'','+@columndata+','')''fro
