在查询分析器以“文本显示结果”方法执行 exec UspOutputData 你的表名 得到导出数据的语句,但image,text,ntext,sql_variant 列不出现在语句,以后改进。 存储过程UspOutputData如下: CREATEPROCEDURE dbo.UspOutputData @tablename sysname AS declare@columnvarchar(1000) declare@columndatavarchar(1000) declare@sqlvarchar(4000) declare@xtypetinyint declare@name sysname declare@objectIdint declare@objectname sysname declare@identint set nocount on set@objectId=object_id(@tablename) if@objectIdisnull-- 判斷對象是否存在 begin print'The object not exists' return end set@objectname=rtrim(object_name(@objectId)) if@objectnameisnullorcharindex(@objectname,@tablename)=0--此判断不严密 begin print'object not in current database' return end ifOBJECTPROPERTY(@objectId,'IsTable') <>1-- 判斷對象是否是table begin print'The object is not table' return end select@ident=status&0x80from syscolumns where id=@objectidand status&0x80=0x80 if@identisnotnull print'SET IDENTITY_INSERT '+@TableName+' ON' declare syscolumns_cursor cursor forselect c.name,c.xtype from syscolumns c where c.id=@objectid orderby c.colid open syscolumns_cursor set@column='' set@columndata='' fetchnextfrom syscolumns_cursor into@name,@xtype while@@fetch_status<>-1 begin if@@fetch_status<>-2 begin if@xtypenotin(189,34,35,99,98) --timestamp不需处理,image,text,ntext,sql_variant 暂时不处理 begin set@column=@column+casewhenlen(@column)=0then''else','end+@name set@columndata=@columndata+casewhenlen(@columndata)=0then''else','','','end +casewhen@xtypein(167,175) then'''''''''+'+@name+'+'''''''''--varchar,char when@xtypein(231,239) then'''N''''''+'+@name+'+'''''''''--nvarchar,nchar when@xtype=61then'''''''''+convert(char(23),'+@name+',121)+'''''''''--datetime when@xtype=58then'''''''''+convert(char(16),'+@name+',120)+'''''''''--smalldatetime when@xtype=36then'''''''''+convert(char(36),'+@name+')+'''''''''--uniqueidentifier else@nameend end end fetchnextfrom syscolumns_cursor into@name,@xtype end close syscolumns_cursor deallocate syscolumns_cursor set@sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename print'--'+@sql exec(@sql) if@identisnotnull print'SET IDENTITY_INSERT '+@TableName+' OFF' GO