get backup of a table sql script

Hi suppose if we want to generate insert statement with actual data of a table. then you can use the following SP 


SET ANSI_NULLS ON 
GO 

SET QUOTED_IDENTIFIER OFF 
GO 


CREATE PROC [dbo].[InsertGenerator] 
(@tableName varchar(100)) as 

--Declare a cursor to retrieve column specific information for the specified table 
DECLARE cursCol CURSOR FAST_FORWARD FOR 
SELECT column_name,data_type FROM information_schema.columns WHERE table_name = @tableName 
OPEN cursCol 
DECLARE @string nvarchar(3000) --for storing the first half of INSERT statement 
DECLARE @stringData nvarchar(3000) --for storing the data (VALUES) related statement 
DECLARE @dataType nvarchar(1000) --data types returned for respective columns 
SET @string='INSERT into '+@tableName+'(' 
SET @stringData='' 

DECLARE @colName nvarchar(50) 

FETCH NEXT FROM cursCol INTO @colName,@dataType 

IF @@fetch_status<>0 
begin 
print 'Table '+@tableName+' not found, processing skipped.' 
close curscol 
deallocate curscol 
return 
END 

WHILE @@FETCH_STATUS=0 
BEGIN 
IF @dataType in ('varchar','char','nchar','nvarchar') 
BEGIN 
--SET @stringData=@stringData+'''''''''+isnull('+@colName+','''')+'''''',''+' 
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+'+@colName+'+'''''+''''',''NULL'')+'',''+' 
END 
ELSE 
if @dataType in ('text','ntext') --if the datatype is text or something else 
BEGIN 
SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(2000)),'''')+'''''',''+' 
END 
ELSE 
IF @dataType = 'money' --because money doesn't get converted from varchar implicitly 
BEGIN 
SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+' 
END 
ELSE 
IF @dataType='datetime' 
BEGIN 
--SET @stringData=@stringData+'''convert(datetime,''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+''''''),''+' 
--SELECT 'INSERT Authorizations(StatusDate) VALUES('+'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations 
--SET @stringData=@stringData+'''convert(money,''''''+isnull(cast('+@colName+' as varchar(200)),''0.0000'')+''''''),''+' 
SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+' 
-- 'convert(datetime,'+isnull(''''+convert(varchar(200),StatusDate,121)+'''','NULL')+',121),)' FROM Authorizations 
END 
ELSE 
IF @dataType='image' 
BEGIN 
SET @stringData=@stringData+'''''''''+isnull(cast(convert(varbinary,'+@colName+') as varchar(6)),''0'')+'''''',''+' 
END 
ELSE --presuming the data type is int,bit,numeric,decimal 
BEGIN 
--SET @stringData=@stringData+'''''''''+isnull(cast('+@colName+' as varchar(200)),''0'')+'''''',''+' 
--SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+' 
SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+' 
END 

SET @string=@string+@colName+',' 

FETCH NEXT FROM cursCol INTO @colName,@dataType 
END 
DECLARE @Query nvarchar(4000) 

SET @query ='SELECT '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM '+@tableName 
exec sp_executesql @query 
--select @query 

CLOSE cursCol 
DEALLOCATE cursCol 



GO