生成表记录的SQL语句

IF OBJECT_ID('SP_GET_TABLE_INSERT_SQL') IS NOT NULL DROP PROC SP_GET_TABLE_INSERT_SQL
GO
/*-------------------------------------------------------------------------------  
功能: 
     生成表记录的SQL语句(注TEXT与NTEXT字段导出为NULL)

参数说明:
     @Table_Name   表名
     @IsPrint         是否打印输入[1:是,0:否].
                     是:Print字符串在查询分析器中使用.
                     否:则为Select出表(默认为0:否)
    
例:
     EXEC SP_GET_TABLE_INSERT_SQL 'Customers'      --Select出表
     EXEC SP_GET_TABLE_INSERT_SQL 'Customers',1    --Print出字符串
   

--当表中有自增列[identity]时,将生成的SQL语句放入下方 Insert Sql语句 处 ,执行

set identity_insert [thetablename] on
go
..Insert Sql语句
go
set identity_insert [thetablename] off
go

-------------------------------------------------------------------------------------*/  
CREATE PROC [dbo].SP_GET_TABLE_INSERT_SQL(@Table_Name AS sysname,@IsPrint AS BIT=0)  
AS  
BEGIN  
 SET NOCOUNT ON
 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(MAX)  
 DECLARE @str_value  AS VARCHAR(MAX)

 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   = @Table_Name  
  ORDER BY scol.colid 
  
   SET @str_insert = '''insert into [' + @table_name + '] ('  
   SET @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  
       SET @str_insert = @str_insert +'['+ @column_name + '],'    
       BEGIN    
         SET @str_value = @str_value + 'case when ' + @column_name + ' is null then ''null'' else '  
         IF @sys_dtype IN ('char', 'varchar', 'nchar', 'nvarchar')  
         BEGIN  
           SET @str_value = @str_value + ''''''''' + ' +   'replace(' + @column_name + ', '''''''', '''''''''''')' + ' + '''''''''  
         END
         ELSE IF @sys_dtype IN ('text','ntext')  
         BEGIN  
           SET @str_value = @str_value + '''null'''  
         END
         ELSE IF @sys_dtype IN ('datetime','datetime2', 'smalldatetime','date')  
         BEGIN  
           SET @str_value = @str_value + ''''''''' + ' +   'convert(varchar, ' + @column_name + ',120)' + ' + '''''''''  
         END  
         ELSE IF @sys_dtype IN ('bigint', 'int', 'smallint', 'tinyint', 'bit', 'decimal', 'numeric', 'money', 'smallmoney', 'float', 'real')  
         BEGIN  
           SET @str_value = @str_value +   'convert(varchar, ' + @column_name + ')'+ ' '  
         END
         ELSE
         BEGIN
   SET @str_value = @str_value + ''''''''' + ' +   @column_name  + ' + '''''''''  
         END           
         SET @str_value = @str_value + ' end '  
       END    
       SET @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(MAX))
   DECLARE @sql VARCHAR(MAX)   
   INSERT INTO #returnTable
   EXEC ('select ' + @str_insert + ' + ' + @str_value + ' from ' + @table_name + ' ')
   IF @IsPrint = 0
       SELECT * FROM #returnTable
   ELSE
   BEGIN
     DECLARE @PrintString AS VARCHAR(MAX)
     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 
GO