set @ValList = @ValList + ' ''+convert(varchar(200),' + @ColName + ')+'''

set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),[' + @ColName + ']),''null'')+'''''''

CREATE PROCEDURE [dbo].[Script_Table] 
(
	@TableName sysname,
	@IdentityInsert int = 0,
	@DebugMode bit = 0
)

AS

SET NOCOUNT ON 

--declare @TableName sysname
declare @WhereClause  varchar(1024)
--declare @IdentityInsert int
declare @ColName sysname
declare @ColType tinyint
declare @ColStatus tinyint
--declare @DebugMode bit
declare @ColList varchar(8000)
declare @ValList varchar(8000)
declare @SQL1 nvarchar(4000)
declare @SQL2 nchar(10)
declare @SQL3 nchar(1000)



--set @TableName = 'emp' 	--  '<YourTableName>' 
set @WhereClause = '' 			-- limit scope of inserts
--set @DebugMode = 0 			-- set to 1 if you only want a script

--set @IdentityInsert = 1                -- set to 1 if you want to force IDENTITY_INSERT statements

set @ColList = ''
set @ValList = ''
set @SQL1 = 'select replace(''insert into ' + @TableName + ' ('
set @SQL2 = ') values ('
set @SQL3 = ')'', ''''''null'''''', ''null'') from ' + @TableName

if @DebugMode = 1 print '-- StmtShell: ' + @sql1 + @sql2 + @sql3

/*

  select c.name, c.xtype, c.status
  from syscolumns c
    inner join sysobjects o
      on o.id = c.id
  where o.name in (@TableName)
    and o.xtype in ('U', 'S')
  order by ColID

*/
declare csrColumns cursor local fast_forward for
  select c.name, c.xtype, c.status
  from syscolumns c
    inner join sysobjects o
      on o.id = c.id
  where o.name = @tablename
    and o.xtype in ('U', 'S')
  order by ColID

open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus

while @@fetch_status = 0
begin
	if not ((@ColType = 56 AND @ColStatus = 128) AND  (@IdentityInsert =0))
		set @ColList = @ColList + ' ' + @ColName

  if @ColType in (127,173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165)    -- numeric types (nulls not supported yet)
		if(@ColType = 56 AND @ColStatus = 128) AND (@IdentityInsert =0)
			set @ValList = @ValList 
		else
		set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),[' + @ColName + ']),''null'')+'''''''
  else if @ColType in (175, 239, 231, 231, 167)                            -- uid and string types
    set @ValList = @ValList + ' ''''''+isnull(replace([' + @ColName + '],'''''''',''''''''''''),''null'')+'''''''
  else if @ColType in (36,58, 61)                                             -- dates , uniqueidentifiers(nulls not supported yet)
	set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),[' + @ColName + ']),''null'')+'''''''
    
  if @DebugMode = 1             begin print '-- @ValList: ' + rtrim(@ValList) end 
  if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end          -- Check if column has Identity attribute
  fetch next from csrColumns into @ColName, @ColType, @ColStatus
end

close csrColumns
deallocate csrColumns

set @ColList = replace(ltrim(@ColList), ' ', ', ')
set @ValList = replace(ltrim(@ValList), ' ', ', ')

if @IdentityInsert = 1
  print 'set identity_insert ' + @TableName + ' on'

if @DebugMode = 1
  print  + @SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause
else
  exec (@SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause)



if @IdentityInsert = 1
  print 'set identity_insert ' + @TableName + ' off'
SET NOCOUNT OFF


GO

/*
Use this script to create insert statements for each row in the specified table.

Instructions:
1. Set the database you want to script from as normal.

2. change the set @TableName = '<YourTableName>' line to be the
table you want to script out.

3. Run the script and copy all the text from the results below
the line with all the dashes (----).

Notes:
   If you get the error message "Invalid object name '<YourTableName>'."
   then you either forgot to set the correct database or you spelled
   your table name wrong

Credits:
  Bob Wiechman - Fix for smalldatetime support
  Richard Lesh - correct support of uniqueidentifiers, automatic
	  setting of Identity off/on, add Where clause support, more detail in 
  	  debug mode.
  Fuhai Li - correct support of null values;
			 fix the coallation conflicts via using default coallation
			 correct the identity insert on/off for non debug mode
			 column name with bracket to make it robust for naming 
  To do: test the null values for gui type
  To do: add on parameters to choose which fields would export via colorder such as 1,2,...	
*/

declare @TableName sysname
declare @WhereClause  varchar(1024)
declare @IdentityInsert int
declare @ColName sysname
declare @ColType tinyint
declare @ColStatus tinyint
declare @DebugMode bit
declare @ColList nvarchar(4000)
declare @ValList nvarchar(4000)
declare @SQL1 nvarchar(1000)
declare @SQL2 nchar(10)
declare @SQL3 nchar(1000)

set @TableName = 'Region' 	--  '<YourTableName>' 
set @WhereClause = '' 		-- limit scope of inserts, this will be hard coded thing to narrow down the set
set @DebugMode = 0 			-- set to 1 if you only want a script

set @IdentityInsert = 0     -- set to 1 if you want to force IDENTITY_INSERT statements

set @ColList = ''
set @ValList = ''
set @SQL1 = 'select replace(''insert into ' + @TableName + ' ('
set @SQL2 = ') values ('
set @SQL3 = ')'' COLLATE DATABASE_DEFAULT, ''''''null'''''', ''null'') from ' + @TableName

if @DebugMode = 1 print '-- StmtShell: ' + @sql1 + @sql2 + @sql3

declare csrColumns cursor local fast_forward for
  select c.name, c.xtype, c.status
  from syscolumns c
    inner join sysobjects o
      on o.id = c.id
  where o.name = @TableName
    and o.xtype in ('U', 'S')
  order by ColID

open csrColumns
fetch next from csrColumns into @ColName, @ColType, @ColStatus
/*
while @@fetch_status = 0
begin
  set @ColList = @ColList + ' ' + @ColName
  if @ColType in (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165)    -- numeric types (nulls not supported yet)
    set @ValList = @ValList + ' ''+convert(varchar(200),' + @ColName + ')+'''
  else if @ColType in (175, 239, 231, 231, 167)                            -- uid and string types
    set @ValList = @ValList + ' ''''''+isnull(' + @ColName + ',''null'')+'''''''
  else if @ColType in (58, 61)                                             -- dates (nulls not supported yet)
    set @ValList = @ValList + ' ''''''+convert(varchar(200),' + @ColName + ')+'''''''
  else if @ColType = 36                                                    -- uniqueidentfiers (nulls not supported yet)
    set @ValList = @ValList + ' ''''{''+convert(varchar(200),' + @ColName + ')+''}'''''
  if @DebugMode = 1             begin print '-- @ValList: ' + rtrim(@ValList) end 
  if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end          -- Check if column has Identity attribute
  fetch next from csrColumns into @ColName, @ColType, @ColStatus
end
*/
while @@fetch_status = 0
begin
  --set @ColList = @ColList + ' ' + @ColName
  set @ColList = @ColList + ' ' + '['+ @ColName + ']'
  if @ColType in (173, 104, 106, 62, 56, 60, 108, 59, 52, 122, 48, 165)    -- numeric types 
    set @ValList = @ValList + ' ''+isnull(convert(varchar(200),' + @ColName + '),''null'')+'''
  else if @ColType in (175, 239, 231, 231, 167)                            -- uid and string types
    set @ValList = @ValList + ' ''''''+isnull(' + @ColName + ',''null'')+'''''''
  else if @ColType in (58, 61, 36)                                             -- dates and uniqueidentfiers
    set @ValList = @ValList + ' ''''''+isnull(convert(varchar(200),' + @ColName + '),''null'')+'''''''
  if @DebugMode = 1             begin print '-- @ValList: ' + @ValList end 
  if (@ColStatus & 0x80) = 0x80 begin set @IdentityInsert = 1 end          -- Check if column has Identity attribute
  fetch next from csrColumns into @ColName, @ColType, @ColStatus
end

close csrColumns
deallocate csrColumns

set @ColList = replace(ltrim(@ColList), ' ', ', ')
set @ValList = replace(ltrim(@ValList), ' ', ', ')

if @IdentityInsert = 1 and @DebugMode = 1
  print 'set identity_insert ' + @TableName + ' on'
if @IdentityInsert = 1 and @DebugMode = 0
  select 'set identity_insert ' + @TableName + ' on'

if @DebugMode = 1
  print @SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause
else
  exec (@SQL1 + @ColList + @SQL2 + @ValList + @SQL3 + ' ' + @WhereClause)

if @IdentityInsert = 1 and @DebugMode = 1
  print 'set identity_insert ' + @TableName + ' off'
if @IdentityInsert = 1 and @DebugMode = 0
  select 'set identity_insert ' + @TableName + ' off'