SQL Server 2005 导出 数据脚本

SQL Server 2005没有直接生成表内容的脚本功能

需要手动实现

CREATE	PROCEDURE dbo.UspOutputData 
@tablename sysname 
AS 
BEGIN
	declare	@column	varchar(1000) 
	declare	@columndata varchar(1000) 
	declare	@sql varchar(4000) 
	declare	@xtype tinyint 
	declare	@name sysname 
	declare	@objectId int 
	declare	@objectname sysname 
	declare	@ident int 

	set nocount on 
	set @objectId=object_id(@tablename) 

	if @objectId is	null --	判断对象是否存在 
	begin 
	  print 'The object not exists' 
	  return 
	end 
	set @objectname=rtrim(object_name(@objectId)) 

	if @objectname is null or charindex(@objectname,@tablename)=0 --此判断不严密 
	begin 
	  print 'object not in current database' 
	  return 
	end 

	if OBJECTPROPERTY(@objectId,'IsTable') < > 1 --	判断对象是否是table 
	begin 
	  print 'The object is not table'	
	  return 
	end 

	select @ident=status&0x80 from syscolumns where	id=@objectid and status&0x80=0x80 

	if @ident is not null 
	  print 'SET IDENTITY_INSERT ['+@TableName+'] ON'	
	print('alter table ['+@TableName+'] nocheck constraint all')--停用约束
	declare	syscolumns_cursor cursor 
	   for
	       select c.name,c.xtype 
	       from syscolumns c 
	       where c.id=@objectid order by c.colid 

	open syscolumns_cursor 
	set @column='' 
	set @columndata='' 
	fetch next from	syscolumns_cursor into @name,@xtype 


	while @@fetch_status < >-1 
	begin 
	  if @@fetch_status < >-2	
	  begin 
		if @xtype not in(189,34,35,99,98) --timestamp不需处理，image,text,ntext,sql_variant 暂时不处理 
	        begin 
		      set @column=@column+case when len(@column)=0 then'' else ','end+@name 
		      set @columndata=@columndata+case when len(@columndata)=0 then '' else ','','','  end 
			  +case when @xtype in(167,175) then '''''''''+'+@name+'+''''''''' --varchar,char	
			  when @xtype in(231,239)	then '''N''''''+'+@name+'+''''''''' --nvarchar,nchar 
			  when @xtype=61 then '''''''''+convert(char(23),'+@name+',121)+'''''''''	--datetime 
			  when @xtype=58 then '''''''''+convert(char(16),'+@name+',120)+'''''''''	--smalldatetime	
			  when @xtype=36 then '''''''''+convert(char(36),'+@name+')+''''''''' --uniqueidentifier 
			  else @name end 
	        end 
	  end 
	  fetch next from	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 @ident is not null 
	print 'SET IDENTITY_INSERT ['+@TableName+'] OFF' 
	print('alter table ['+@TableName+'] check constraint all')--恢复约束
END
GO 

create PROCEDURE OutputAllData
as
begin
	declare	@table varchar(1000)
	declare	syscolumns_cursor1 cursor   
	for 
	  select TABLE_NAME	from INFORMATION_SCHEMA.TABLES 
	  where	TABLE_TYPE='BASE TABLE'
	open syscolumns_cursor1
	fetch next from	syscolumns_cursor1 into	@table
	while @@fetch_status < >-1 
	begin
	   exec('exec UspOutputData '''+@table+'''')
	   fetch next from syscolumns_cursor1 into @table
	end
	close syscolumns_cursor1  
	deallocate syscolumns_cursor1	
end
Go
exec OutputAllData

 单独使用方法exec UspOutputData ‘User’