生成表记录的SQL语句

/*------------------------------------------------------------------------------- 
功能:
    生成表记录的SQL语句

参数说明:
    @pi_table_name  表名
    @IsPrint        是否打印输入[1:是,0:否].
                    是:Print字符串在查询分析器中使用.
                    否:则为Select出表(默认为0:否)
   
例:
    exec sms_fn_get_table_sql 'Customers'     --Select出表
    exec sms_fn_get_table_sql 'Customers',1   --Print出字符串
-------------------------------------------------------------------------------------*/ 
create  proc  [dbo].sms_fn_get_table_sql(@pi_table_name as sysname,@IsPrint as bit=0) 
as 
begin 
  set nocount on
  declare @table_name        as sysname 
  declare @obj_name          as sysname 
  declare @column_name       as sysname 
  declare @usr_defined_dtype as sysname 
  declare @sys_dtype         as sysname 
 
  declare @str_insert as varchar(900) 
  declare @str_value  as varchar(7000) 
 
  select @table_name = @pi_table_name 
 
  declare @cu_obj cursor 
  set @cu_obj = cursor local scroll for 
    select sobj.name  as obj_name, 
           scol.name  as column_name, 
           styp.name  as usr_defined_dtype, 
           styp1.name as sys_dtype 
    from   sysobjects sobj 
    inner  join syscolumns scol 
           on   scol.id         = sobj.id 
    inner  join systypes styp 
           on   styp.xtype      = scol.xtype 
           and  styp.xusertype  = scol.xusertype 
    inner  join systypes styp1 
           on   styp1.xtype     = styp.xtype 
           and  styp1.xusertype = styp.xtype 
    where  sobj.xtype = 'U' 
    and    sobj.name  = @pi_table_name 
    order by scol.colid 
 
 
  select @str_insert = '''insert into ' + @table_name + ' (' 
  select @str_value  = '''values ('' + ' 
 
  open @cu_obj 
  fetch next from @cu_obj into @obj_name, @column_name, @usr_defined_dtype, @sys_dtype 
 
  while @@fetch_status = 0 
  begin 
    if @sys_dtype <> 'image' 
    begin 
      select @str_insert = @str_insert + @column_name + ', ' 
 
      begin 
 
        select @str_value = @str_value + 'case when ' + @column_name + ' is null then ''null'' else ' 
        if @sys_dtype in ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext') 
        begin 
          select @str_value = @str_value + ''''''''' + ' +  'replace(' + @column_name + ', '''''''', '''''''''''')' + ' + ''''''''' 
        end 
        else if @sys_dtype in ('datetime', 'smalldatetime') 
        begin 
          select @str_value = @str_value + ''''''''' + ' +  'convert(varchar, ' + @column_name + ',121)' + ' + ''''''''' 
        end 
        else if @sys_dtype in ('bigint', 'int', 'smallint', 'tinyint', 'bit', 'decimal', 'numeric', 'money', 'smallmoney', 'float', 'real') 
        begin 
          select @str_value = @str_value +  'convert(varchar, ' +  @column_name + ')'+ ' ' 
        end 
        select @str_value = @str_value + ' end ' 
      end 
 
      select @str_value = @str_value + '+ '', '' + ' 
 
    end 
 
    fetch next from @cu_obj into @obj_name, @column_name, @usr_defined_dtype, @sys_dtype 
 
  end 
  close @cu_obj 
 
  select @str_insert = left(@str_insert, len(@str_insert)-1) + ') '' ' 
  select @str_value  = left(@str_value, len(@str_value)-8) + ' + '') ''  '
 
  create table #returnTable (sqlString varchar(8000))
  declare @sql varchar(8000)
  insert into #returnTable
  exec ('select ' + @str_insert + ' + ' + @str_value + ' from ' + @table_name + ' ')
  if @IsPrint = 0
      select * from #returnTable
  else
  begin
    declare @PrintString as varchar(8000)
    DECLARE print_cursor CURSOR FOR
    SELECT sqlString
    FROM #returnTable

    OPEN print_cursor

    FETCH NEXT FROM print_cursor INTO @PrintString
    WHILE @@FETCH_STATUS = 0
    BEGIN     
      PRINT @PrintString
      FETCH NEXT FROM print_cursor INTO @PrintString  
    END

    CLOSE print_cursor
    DEALLOCATE print_cursor
  end
  drop table #returnTable
  set nocount off
end