Export table data to SQL scripts using Stored Procedures

 

Code
Create procedure DBO. uspoutputdata
@ Tablename sysname
As
Declare @ column varchar ( 1000 )
Declare @ columndata varchar ( 1000 )
Declare @ SQL varchar ( 4000 )
Declare @ xtype tinyint
Declare @ name sysname
Declare @ objectid Int  
Declare @ objectname sysname
Declare @ ident Int

SetNocount on
Set@ Objectid=Object_id (@ tablename)

If @ objectid is null -- determine whether an object exists
begin
Print ' the object not exists '
return
end
set @ objectname = rtrim (object_name (@ objectid)

If @ objectname is null or charindex (@ objectname, @ tablename) = 0 -- this judgment is not strict
begin
Print ' object not in current database '
return
end

If objectproperty (@ objectid, ' istable ' ) > 1 -- determine whether the object is a table
begin
Print ' the object is not table '
return
end

select @ ident = Status & 0x80 from syscolumns where id = @ objectid and status & 0x80 = 0x80

If@ IdentIsNotNull 
Print'Set identity_insert'+@ Tablename+'On' 

Declare syscolumns_cursor cursor

ForSelect C. Name, C. xtype from syscolumns CWhereC. ID=@ Objectid order by C. colid

Open syscolumns_cursor
Set@ Column='' 
Set@ Columndata='' 
Fetch next from syscolumns_cursor into @ name, @ xtype

While @ Fetch_status <   >- 1  
Begin
If @ Fetch_status <   >- 2  
Begin
If @ Xtype not In ( 189 , 34 , 35 , 99 , 98 ) -- Timestamp does not need to be processed. Image, text, ntext, and SQL _variant are not processed at the moment.

begin
set @ column = @ column + case when Len (@ column) = 0 then '' else ' , ' end + @name

Set@ Columndata=@ Columndata+CaseWhen Len (@ columndata)=0Then'' Else ','','',' 
End

+ Case When @ xtype In ( 167 , 175 ) Then ''''''''' + ' + @ Name + ' + '''''''''   -- Varchar, Char  
When @ xtype In ( 231 , 239 ) Then ''' N '''''' + ' + @ Name + ' + '''''''''   -- Nvarchar, nchar
When @ xtype = 61 Then ''''''''' + Convert (char (23 ), ' + @ Name + ' , 121) + '''''''''   -- Datetime
When @ xtype = 58 Then ''''''''' + Convert (char (16 ), ' + @ Name + ' , 120) + '''''''''   -- Smalldatetime
When @ xtype = 36 Then ''''''''' + Convert (char (36 ), ' + @ Name + ' ) + '''''''''   -- Uniqueidentifier
Else @ Name end

End

End

Fetch next from syscolumns_cursor into @ name, @ xtype

End

Close syscolumns_cursor
Deallocate syscolumns_cursor

Set @ SQL = ' Set nocount on select '' Insert ' + @ Tablename + ' ( ' + @ Column + ' ) Values ( '' As '' -- '' , ' + @ Columndata + ' , '' ) '' From ' + @ Tablename

Print'--'+@ SQL
Exec (@ SQL)

If@ IdentIsNotNull 
Print'Set identity_insert'+@ Tablename+'Off'
Go
 

Run exec uspoutputdata in the query analyzer. Your table name

Text display result.