通过脚本获取指定表的所有Insert语句

--通过脚本获取指定表的所有Insert语句。 

Create         procedure [dbo].[sp_GetInsertSQL] 
                 @tablename_mask varchar(30) = NULL 
as 
begin 
-- NOTE: If, when executing in the Query Analyzer, the result is truncated, you can remedy 
--       this by choosing Query / Current Connection Options, choosing the Advanced tab and 
--       adjusting the value of 'Maximum characters per column'. 
--       Unchecking 'Print headers' will get rid of the line of dashes. 
 
  declare @tablename       varchar (128) 
  declare @tablename_max   varchar (128) 
  declare @tableid         int 
  declare @columncount     numeric (7,0) 
  declare @columncount_max numeric (7,0) 
  declare @columnname      varchar (100) 
  declare @columntype      int 
  declare @string          varchar (30) 
  declare @leftpart        varchar (max)    
  declare @rightpart       varchar (max)    
  declare @hasident        int 
 
  set nocount on 
 
  -- take ALL tables when no mask is given (!) 
  if (@tablename_mask is NULL) 
  begin 
    select @tablename_mask = '%' 
  end 
 
  -- create table columninfo now, because it will be used several times 
 
  create table #columninfo 
  (num      numeric (7,0) identity, 
   name     varchar(100), 
   usertype smallint) 
 
 
  select name, 
         id 
    into #tablenames 
    from sysobjects 
   where type in ('U' ,'S') 
     and name like @tablename_mask 
 
  -- loop through the table #tablenames 
 
  select @tablename_max  = MAX (name), 
         @tablename      = MIN (name) 
    from #tablenames 
 
  while @tablename <= @tablename_max 
  begin 
    select @tableid   = id 
      from #tablenames 
     where name = @tablename 
 
    if (@@rowcount <> 0) 
    begin 
      -- Find out whether the table contains an identity column 
      select @hasident = max( status & 0x80 ) 
        from syscolumns 
       where id = @tableid 
 
      truncate table #columninfo 
 
      insert into #columninfo (name,usertype) 
      select name, type 
        from syscolumns C 
       where id = @tableid 
        -- and type <> 37            -- do not include timestamps 
 
      -- Fill @leftpart with the first part of the desired insert-statement, with the fieldnames 
 
      select @leftpart = 'select ''insert into ['+@tablename 
      select @leftpart = @leftpart + '] (' 
 
      select @columncount     = MIN (num), 
             @columncount_max = MAX (num) 
        from #columninfo 
      while @columncount <= @columncount_max 
      begin 
        select @columnname = name, 
               @columntype = usertype 
          from #columninfo 
         where num = @columncount 
        if (@@rowcount <> 0) 
        begin 
          if (@columncount < @columncount_max) 
          begin 
            select @leftpart = @leftpart + '[' + @columnname + '],' 
          end 
          else 
          begin 
            select @leftpart = @leftpart + '[' + @columnname + '])' 
          end 
        end 
 
        select @columncount = @columncount + 1 
      end 
 
      select @leftpart = @leftpart + ' values(''' 
 
      -- Now fill @rightpart with the statement to retrieve the values of the fields, correctly formatted 
 
      select @columncount     = MIN (num), 
             @columncount_max = MAX (num) 
        from #columninfo 
 
      select @rightpart = '' 
 
      while @columncount <= @columncount_max 
      begin 
        select @columnname = name, 
               @columntype = usertype 
          from #columninfo 
         where num = @columncount 
 
        if (@@rowcount <> 0) 
        begin 
 
          if @columntype in (39,47) 
          begin 
            select @rightpart = @rightpart + '+' 
            select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace([' + @columnname + '],' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6) + ')+' + replicate( char(39), 4 ) + ',''NULL'')' 
          end 
 
          else if @columntype = 35 
                                   
          begin 
            select @rightpart = @rightpart + '+' 
            select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+replace(convert(varchar(1000),[' + @columnname + '])' + ',' + replicate( char(39), 4 ) + ',' + replicate( char(39), 6 ) + ')+' + replicate( char(39), 4 ) + ',''NULL'')'
 
          end 
 
          else if @columntype in (58,61,111) 
          begin 
            select @rightpart = @rightpart + '+' 
            select @rightpart = @rightpart + 'ISNULL(' + replicate( char(39), 4 ) + '+convert(varchar(20),[' + @columnname + '])+'+ replicate( char(39), 4 ) + ',''NULL'')' 
          end 
 
          else   
          begin 
            select @rightpart = @rightpart + '+' 
            select @rightpart = @rightpart + 'ISNULL(convert(varchar(99),[' + @columnname + ']),''NULL'')' 
          end 
 
 
          if ( @columncount < @columncount_max) 
          begin 
            select @rightpart = @rightpart + '+'',''' 
          end 
 
        end 
        select @columncount = @columncount + 1 
      end 
 
    end 
 
    select @rightpart = @rightpart + '+'')''' + ' from [' + @tablename 
 
    -- Order the select-statements by the first column so you have the same order for 
    -- different database (easy for comparisons between databases with different creation orders) 
    select @rightpart = @rightpart + '] order by 1' 
 
    -- For tables which contain an identity column we turn identity_insert on 
    -- so we get exactly the same content 
 
    if @hasident > 0 
       select 'SET IDENTITY_INSERT ' + @tablename + ' ON' 
 
 --print @leftpart + @rightpart 
    exec ( @leftpart + @rightpart ) 
  
 
    if @hasident > 0 
       select 'SET IDENTITY_INSERT ' + @tablename + ' OFF' 
 
    select @tablename      = MIN (name) 
      from #tablenames 
     where name            > @tablename 
  end 
 
end