set nocount on

declare cur scroll cursor for select id, name from sysobjects where type = 'U' order by name
declare @table varchar(255)
declare @id int
open cur
fetch next from cur into @id, @table

create table #sql
(
	queries text
)
create table #columns
(
	name varchar(255),
	type int
)

while @@fetch_Status = 0
begin
	declare cols scroll cursor for select name, status & 128 as isIdentity, xusertype as usertype from Syscolumns where id = @id
	open cols
	declare @column varchar(255)
	declare @isIdentity int
	declare @usertype int
	fetch next from cols into @column, @isIdentity, @usertype

	declare @insert varchar(8000)
	declare @alter varchar(8000)
	declare @realter varchar(8000)
	declare @columns varchar(8000)
	declare @columnsString varchar(8000)
	select @columns = ''
	select @columnsString = ''

	select @insert = 'INSERT INTO ' + @table + ' ('
	select @alter = ''
	select @realter = ''
	
	while @@fetch_Status = 0
	begin		
		if (@isIdentity > 0)
		begin
			-- alter table to remove the identity column
			select @alter = 'SET IDENTITY_INSERT ' + @table + ' ON'
			select @realter = 'SET IDENTITY_INSERT ' + @table + ' OFF'			
		end		

		-- gather the insert columns and make the insert template
		if (@columns <> '')
		begin
			select @columns = @columns + ', '
			select @columnsString = @columnsString + ' + '', '' + '
		end
		select @columns = @columns + @column
		declare @nullValue varchar(50)

		if (@userType = 167 or @userType = 35)
		begin
			select @columnsString = @columnsString + ' '''''''' + '
			select @nullValue = '''null'''
		end
		else if (@usertype = 61)
		begin
			select @columnsString = @columnsString + ' ''CONVERT(datetime, '''''' + '
			select @nullValue = ''''''
		end
		else
		begin
			select @nullValue = '0'
		end
		
		select @columnsString = @columnsString + 'replace(cast(isnull([' + @column + '], ' + @nullValue + ') as varchar(8000)), '''''''', '''''''''''' ) '

		if (@userType = 167 or @userType = 35)
		begin
			select @columnsString = @columnsString + ' + '''''''' '
		end
		else if (@userType = 61)
		begin
			select @columnsString = @columnsString + ' + '''''')'''
		end

		insert into #columns select @column, @usertype

		fetch next from cols into @column, @isIdentity, @usertype
	end
	close cols
	deallocate cols

	select @insert = @insert + @columns + ') VALUES ('

	-- add the alter statement
	if (@alter <> '')
	begin
		insert into #sql select @alter
	end

	-- add all insert statements
	declare @execStatement varchar(8000)	
	select @execStatement = 'SELECT ' + '''' + @insert +  ''' + ' + @columnsString + ' + '')''' + ' FROM ' + @table
	select @execStatement = 'INSERT INTO #sql ' + @execStatement

	insert into #sql select 'DELETE FROM ' + @table
	
	--uncomment here to see how the statement looks like
	--insert into #sql select @execStatement
	exec sp_sqlexec @execStatement
	

	-- add realter statement
	if (@realter <> '')
	begin
		insert into #sql select @realter
		insert into #sql select 'DBCC CHECKIDENT (' + @table + ', RESEED)'
	end

	fetch next from cur into @id, @table
end

close cur
deallocate cur

select * from #sql
--select * from #columns

drop table #sql
drop table #columns