在sqlserver2000中将数据导出成Insert脚本

+v2|1H,Z!qrB0if exists (select * from sysobjects where type='p' and  name='UspOutputData')
/}I7`!R RYW:g0   drop proc UspOutputData
0mW k#L} e5K0GO51Testing软件测试网+|'\Asreb%M

X8qMM'fk~O0CREATE PROCEDURE dbo.UspOutputData
T r(axAH9X4o0@tablename sysname 51Testing软件测试网&~ j _+^6v$Ni.Sm!k
AS
XX2u|,S'\(M,Qe7j0declare @column varchar(2000)
z"z5t)c.\T _"x.Q0declare @columndata varchar(2000) 51Testing软件测试网 jiL Tq
declare @sql varchar(8000)
YI:o?-g1x0declare @xtype tinyint 51Testing软件测试网 ~
}+pNrV
declare @name sysname 51Testing软件测试网-L`T [ UE5Z n(\
declare @objectId int 51Testing软件测试网a
XPp%d
declare @objectname sysname 51Testing软件测试网.d+S%C3u"j
declare @ident int

I7t(L-YA`^0set nocount on

-- 判斷對象是否存在
/jl7@6q|%C{(Nu0set @objectId=object_id(@tablename)
;F)dKLT#TvQ0if @objectId is null 51Testing软件测试网%] H rX&p5Qx(X
begin 51Testing软件测试网BJ-Erlx!u7ZG5f
}
print 'The object not exists' 51Testing软件测试网Z{lC:X4c
return 51Testing软件测试网7dp:p'i,E
end 51Testing软件测试网 OTyyK|4F.zk

--此判断不严密 51Testing软件测试网5UMh^.H!Z~*UYG
set @objectname=rtrim(object_name(@objectId)) 51Testing软件测试网1lr3l(m%C)UP
if @objectname is null or charindex(@objectname,@tablename)=0
zw c2M}+ff.W.h0begin
a5_)a;]V0print 'object not in current database' 51Testing软件测试网] sm
U0B$`3N X
return
2U e,]._DL1S+mq0end

-- 判斷對象是否是table
/{E0q~2M`0if OBJECTPROPERTY(@objectId,'IsTable') < > 1 51Testing软件测试网]#RXM8F'e5rf%f0X8z
begin 51Testing软件测试网7v#`.@h
T^+?H
print 'The object is not table' 51Testing软件测试网$B"k/Z(@8]$Fo
return 51Testing软件测试网og7bZ3Y.P
end

--不知道打印的意义
2H%O-S^4`0select @ident=status&0x80 from syscolumns where id=@objectid and status&0x80=0x80 51Testing软件测试网7F2b bN,G1U+T o
if @ident is not null 51Testing软件测试网1ql#_K2p6EK3X`t!nj
print 'SET IDENTITY_INSERT '+@TableName+' ON' 51Testing软件测试网KE|(a.S'b

\"B'E N1r0r051Testing软件测试网-i
}M'L5l}"s^nn-mU
declare syscolumns_cursor cursor
DN4IRs$]0for select c.name,c.xtype from syscolumns c where c.id=@objectid order by c.colid 51Testing软件测试网+y HJ
C.rcbP-Sr

1c0S2`(D8n$JJp0open syscolumns_cursor
:n)pX FNr0set @column=''
to
WDd.L0set @columndata=''
5s ^g#M H#B0fetch next from syscolumns_cursor into @name,@xtype 51Testing软件测试网LbtH%o6}

while @@fetch_status < >-1
E{(`-a+@o6qw0  begin 51Testing软件测试网"A `W
S.|_N
    if @@fetch_status < >-2
2?F-A}uh0      begin
,uVOuD0        --if @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理
&u~*E4A,H;B6L0          begin 51Testing软件测试网c)a[2k]e.`}_\
            set @column=@column+case when len(@column)=0 then'' else ','end+@name  51Testing软件测试网2b'Oy`o]q4}
            set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','end 51Testing软件测试网
p] C/n`x ^+Fm.q
                +case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char
Y&P3B#M-slAr {
u0                      when @xtype in(231,239) then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar
F$x P!@4V;b/??0                      when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+''''''''' --datetime
6n]!D;D1??PfsNC0                      when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+''''''''' --smalldatetime 51Testing软件测试网1I:o;L;d9U-h3pwxg:Y
                      when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier 51Testing软件测试网 H#b0Gt*j5p]3T(s
                      else @name end 51Testing软件测试网)tZ.Wt)v
          end
h9Agw&t0      end
1S"@ a$q~\-ZrrE!D T0    fetch next from syscolumns_cursor into @name,@xtype
4m[5i.iOrq#j:@0  end

close syscolumns_cursor

r2Ii@J e @'od0deallocate syscolumns_cursor 51Testing软件测试网0x:u;N6W a"BXW

&[ e3_3wA@3e0w:^0set @sql='set nocount on select ''insert '+@tablename+'('+@column+') values(''as ''--'','+@columndata+','')'' from '+@tablename 51Testing软件测试网,W;pI^}[$OiY

{)TF$D q9^N0print '--'+@sql 51Testing软件测试网's1l%uQz"L#ZU
exec(@sql) 51Testing软件测试网.vO~*h.I%k"T

--不知道打印的意义
/SC"Z8O*k0if @ident is not null 51Testing软件测试网dc3Ep4wQ
print 'SET IDENTITY_INSERT '+@TableName+' OFF' 51Testing软件测试网]%?$A3A/c

GO51Testing软件测试网[ mJ0P R&k@gd1T
--导出insert语句51Testing软件测试网D8A*\e J'T
exec UspOutputData analysisobject
kr/df H7G0-----------------------------------------51Testing软件测试网0i#C}\ {G'dj7b e
 
2?l&U#Lnj(B,j#l4b0--最后一句 exec UspOutputData analysisobject   改成相应的表名即可.

9{%[D0Xx#IH0 51Testing软件测试网/F#^0Y R h*t ^
 
+koVgy0--exec UspOutputData 表名