使用存储过程 从数据库导出insert语句

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetInsertSqlFromTable]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)   
drop procedure [dbo].[GetInsertSqlFromTable]   
GO   
  
SET QUOTED_IDENTIFIER ON    
GO   
SET ANSI_NULLS ON    
GO   
  
CREATE   proc GetInsertSqlFromTable (@tablename varchar(256))   
  
as  
  
begin  
  
declare @sql varchar(8000)   
  
declare @sqlValues varchar(8000)   
  
set @sql =' ('  
  
set @sqlValues = 'values (''+'  
  
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'    
  
  from    
  
      (select case    
  
						--48:tinyint,52:smallint,56:int,59:real,60:money,62:float,104:bit,106:decimal,108:numeric,122:smallmoney,127:bigint
                when xtype in (48,52,56,59,60,62,104,106,108,122,127)                                   
  
                     then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'  
						--58:smalldatetime,61:datetime,40:date
                 when xtype in (58,61,40)   
  
                      then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'  
  
						--167:varchar,36:uniqueidentifier
               when xtype in (167,36)   
  
                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'  
						--231:nvarchar
                when xtype in (231)   
  
                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'  
  
						--175:char
                when xtype in (175)   
  
                     then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'  
  
						--239:nchar
                when xtype in (239)   
  
                     then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar)  + '))+'''''''''+' end'  
  
                else '''NULL'''  
  
              end as Cols,name  
  
         from syscolumns     
  
        where id = object_id(@tablename)    
  
      ) T    
  
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename   
  
--print @sql   
  
exec (@sql)   
  
end  
GO   
SET QUOTED_IDENTIFIER OFF    
GO   
SET ANSI_NULLS ON    
GO 

exec GetInsertSqlFromTable 'program'


select  name,xtype from systypes

</pre><pre name="code" class="sql">