IF OBJECT_ID('dbo.sp__loadserver') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp__loadserver
    IF OBJECT_ID('dbo.sp__loadserver') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp__loadserver >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp__loadserver >>>'
END
go
create procedure sp__loadserver with encryption as

set nocount on

declare  @database  	char(30),
	@table		char(30),
	@sql		char(255),
	@command	char(255),
	@fetch_outer	integer,
	@fetch_inner	integer

--Initialize variables
select getdate()
select @database = null
select @table = null
select @sql = null
select @command = null
select @fetch_outer = -1
select @fetch_inner = -1

--Create temp table (this is the only way around the need to change database context)
print 'Creating temp table'
create table #tables
(name	char(30)   not null)

--Declare outer cursor
print 'Declaring database cursor'
declare cur_db cursor for
	select name from master..sysdatabases
	where name not in ('master','model','tempdb','msdb')
	for read only

--Open outer cursor and perform initial fetch
print 'Opening database cursor and performing initial fetch'
open cur_db
fetch cur_db into @database
--Save @@fetch_status
select @fetch_outer = @@fetch_status
--Loop through all databases
while @fetch_outer = 0
	begin

	--Turn on trunc on checkpoint to ensure tran log does not fill up during the BCP.
	--Add in the correct destination server and password
	select 'Turning on truncate on checkpoint for database: ' + ltrim(rtrim(@database))
	select @command = "master..xp_cmdshell " + "'" + 'isql -Usa -Ppassword -Sdestination -Q"exec sp_dboption ' + "''" + rtrim(ltrim(@database)) + "''" +
		 ",''trunc. log on chkpt.'',true" + '"' + "'"
	exec (@command)


	--Turn on select into bulk copy
	--Add in the correct destination server and password
	select 'Turning on select into/BCP for database: ' + ltrim(rtrim(@database))
	select @command = "master..xp_cmdshell " + "'" + 'isql -Usa -Ppassword -Sdestination -Q"exec sp_dboption ' + "''" + rtrim(ltrim(@database)) + "''" +
		 ",''select into/bulkcopy'',true" + '"' + "'"
	exec (@command)
	
	--Fill temp table
	print 'Building temp table with list of tables'
	select @command = 'insert into #tables select name from ' + rtrim(ltrim(@database)) + "..sysobjects where type = 'U'"
	exec (@command)
	select name from #tables

	--Declare, open, and perform initial fetch of inner cursor
	print 'Declare, open, and perform and initial fetch on tables cursor'
	declare cur_tbl cursor for select name from #tables for read only
	open cur_tbl
	fetch cur_tbl into @table

	--Save @@fetch_status
	select @fetch_inner = @@fetch_status
	--Loop through all tables
	while @fetch_inner = 0
		begin
	
		--Prepare truncate statement
		select @sql = 'truncate table ' + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table))

		--Execute truncation
		--Add in the correct destination server and password
		select 'Truncating table: ' + ltrim(rtrim(@table))
		select @command = "master..xp_cmdshell " + "'isql -Usa -Ppassword -Sdestination -Q"+'"' + rtrim(ltrim(@sql)) + '"'+"'"
		exec (@command)

		--Prepare and execute BCP out
		--Add in the correct source server and password
		select 'BCP out table:' + ltrim(rtrim(@table))
		select @command = "master..xp_cmdshell 'bcp " + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + 
			' out e:\sql\' + rtrim(ltrim(@table)) + ".out /n /Ssource /Usa /Ppassword'"
		exec (@command)

		--Prepare and execute BCP in
		--Add in the correct destination server and password
		select 'BCP in table: ' + ltrim(rtrim(@table))
		select @command = "master..xp_cmdshell 'bcp " + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + 
			' in e:\sql\' + rtrim(ltrim(@table)) + ".out /n /Sdestination /Usa /Ppassword'"
		exec (@command)

		--Delete output file
		select 'Delete file for table: ' + ltrim(rtrim(@table))
		select @command = "master..xp_cmdshell 'del e:\sql\" + rtrim(ltrim(@table)) + ".out'"
		exec (@command)

		--Fetch next table
		print 'Fetch next table'
		fetch cur_tbl into @table
		select @fetch_inner = @@fetch_status
		end

	--Close and deallocate inner cursor
	print 'Close and deallocate table cursor'
	close cur_tbl
	deallocate cur_tbl

	--Check to see if truncate on checkpoint is should be off.  If it is on, turn it off.
	--Add in the correct destination server and password
	select 'Resetting truncate on checkpoint for database: ' + ltrim(rtrim(@database))
	if (select status & 8 from sysdatabases where name = @database) = 8
		begin
		select @command = "master..xp_cmdshell " + "'" + 'isql -Usa -Ppassword -Sdestination -Q"exec sp_dboption ' + "''" + rtrim(ltrim(@database)) + "''" +
			 ",''trunc. log on chkpt.'',false" + '"' + "'"
		exec (@command)
		end
		
	--Check to see if select into/bcp should be off.  If it is on, turn it off.
	--Add in the correct destination server and password
	select 'Resetting select into bulk copy on database: ' + ltrim(rtrim(@database))
	if (select status & 4 from sysdatabases where name = @database) = 4
		begin
		select @command = "master..xp_cmdshell " + "'" + 'isql -Usa -Ppassword -Sdestination -Q"exec sp_dboption ' + "''" + rtrim(ltrim(@database)) + "''" +
			 ",''select into/bulkcopy'',true" + '"' + "'"
		exec (@command)
		end


	--Fetch next database
	print 'Fetching next database'
	fetch cur_db into @database
	select @fetch_outer = @@fetch_status
	end
--Close and deallocate outer cursor
print 'Close and deallocate database cursor'
close cur_db
deallocate cur_db
print 'Drop temp table'
drop table #tables
select getdate()
set nocount off
go
IF OBJECT_ID('dbo.sp__loadserver') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.sp__loadserver >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp__loadserver >>>'
go