IF OBJECT_ID('dbo.sp__loaddata') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.sp__loaddata
    IF OBJECT_ID('dbo.sp__loaddata') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp__loaddata >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp__loaddata >>>'
END
go
create procedure sp__loaddata with encryption as
/* **************************************************************************/
/* Name:  			sp__loaddata				    */
/* Copyright:		1998, Michael Hotek, Modern Business Technology LLC */
/* Description:		This is used to load data from one server to another*/
/*			it walks down database by database for all tables   */
/*			truncates the destination table, drops the indexes, */
/*			BCPs out the data, BCPs it into the destination,    */
/*			recreates the indexes, and resets database options  */
/* Parameters:		none						    */
/* Return Values:	none at this time				    */
/* Created by:		Michael Hotek, Modern Business Technology LLC	    */
/* Location:								    */
/* Modified:		2/3/98	Created					    */
/*			3/3/98	Added in index drop and creation  Mike Hotek*/
/* **************************************************************************/

set nocount on

declare  @database  	char(30),
	@table				char(30),
	@index				char(30),
	@sql				char(255),
	@command			char(255),
	@sourcepwd			char(30),
	@sourceserver		char(30),
	@destpwd			char(30),
	@destserver			char(30),
	@fetch_outer		integer,
	@fetch_inner		integer,
	@fetch_indexes		integer,
	@var1				varchar(255),
	@var2				varchar(255),
	@var3				varchar(255),
	@var4				varchar(255),
	@var5				varchar(255),
	@var6				varchar(255),
	@var7				varchar(255),
	@var8				varchar(255),
	@var9				varchar(255),
	@var10				varchar(255),
	@var11				varchar(255),
	@var12				varchar(255),
	@var13				varchar(255),
	@var14				varchar(255),
	@var15				varchar(255),
	@var16				varchar(255),
	@var17				varchar(255),
	@var18				varchar(255),
	@var19				varchar(255)

--Initialize variables
select getdate()
select @database = null
select @table = null
select @index = null
select @sql = null
select @command = null
select @fetch_outer = -1
select @fetch_inner = -1
select @fetch_indexes = -1
select @var1 = null
select @var2 = null
select @var3 = null
select @var4 = null
select @var5 = null
select @var6 = null
select @var7 = null
select @var8 = null
select @var9 = null
select @var10 = null
select @var11 = null
select @var12 = null
select @var13 = null
select @var14 = null
select @var15 = null
select @var16 = null
select @var17 = null
select @var18 = null
select @var19 = null

--Substitute the correct source and destination server and password
select @sourcepwd = source password
select @sourceserver = source server
select @destpwd = destination password
select @destserver = destination server

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

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

--Create temp table for create index statements (Need this to get around the 255 char limit on variables)
--This also can not be a temp table as we need to access it via xp_cmdshell which won't see the #temp table
print 'Creating temp table for create index statements'
create table createindexes
(CreateStatement	text)

--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.
	select 'Turning on truncate on checkpoint for database: ' + ltrim(rtrim(@database))
	select @command = "master..xp_cmdshell " + "'" + 'isql -Usa -P' + rtrim(ltrim(@destpwd)) + ' -S' + rtrim(ltrim(@destserver)) + ' -Q"exec sp_dboption ' + "''" + 	rtrim(ltrim(@database)) + "''"  +  ",''trunc. log on chkpt.'',true" + '"' + "'"
	exec (@command)


	--Turn on select into bulk copy
	select 'Turning on select into/BCP for database: ' + ltrim(rtrim(@database))
	select @command = "master..xp_cmdshell " + "'" + 'isql -Usa -P' + rtrim(ltrim(@destpwd)) + ' -S' + rtrim(ltrim(@destserver)) + ' -Q"exec sp_dboption ' + "''" + 	rtrim(ltrim(@database)) + "''"  + ",''select into/bulkcopy'',true" + '"' + "'"
	exec (@command)
	
	--Fill temp table for tables
	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)

	--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

		--Fill temp tables for indexes
		print 'Building temp table with a list of indexes'
		select @command = 'insert into #indexes select a.name from ' + rtrim(ltrim(@database)) + '..sysindexes a, ' +  rtrim(ltrim(@database)) + '..sysobjects b where b.name = "' + ltrim(rtrim(@table)) +	'" and a.indid between 1 and 250 and a.id = b.id'
		exec (@command)
	
		--Declare index cursor
		--Declare, open and perform initial fetch
		declare cur_index cursor for select name from #indexes for read only
		open cur_index
		fetch cur_index into @index
		--Save @@fetch_status
		select @fetch_indexes = @@fetch_status
		--Loop through all indexes
		while @fetch_indexes = 0
			begin
				--Execute drop of index
				select @sql = 'drop index ' + '..' + rtrim(ltrim(@table)) + '.' + rtrim(ltrim(@index))
				select @command = "master..xp_cmdshell " + "'isql -Usa -P" + rtrim(ltrim(@destpwd)) + ' -S' + rtrim(ltrim(@destserver)) + ' -d'  + rtrim(ltrim(@database)) + ' -Q"' + 	rtrim(ltrim(@sql)) + '"' + "'"
				exec (@command)
				fetch cur_index into @index
				select @fetch_indexes = @@fetch_status
			end
		--Close and deallocate cursor
		close cur_index
		deallocate cur_index
		truncate table #indexes

		--Prepare truncate statement
		select @sql = 'truncate table ' + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table))

		--Execute truncation
		select 'Truncating table: ' + ltrim(rtrim(@table))
		select @command = "master..xp_cmdshell " + "'isql -Usa -P" + rtrim(ltrim(@destpwd)) + " -S" + rtrim(ltrim(@destserver)) + " -Q"+'"' + rtrim(ltrim(@sql)) + '"'+"'"
		exec (@command)

		--Prepare and execute BCP out
		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 /S" + rtrim(ltrim(@sourceserver)) + " /Usa /P" + rtrim(ltrim(@sourcepwd)) + "'"
		exec (@command)

		--Prepare and execute BCP in
		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 /S" + rtrim(ltrim(@destserver)) + " /Usa /P" + rtrim(ltrim(@destpwd)) + "'"
		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)

		--Recreate indexes
		select @var1 = "select 'create' + case when i.status & 2 != 0 then ' unique ' end + " 
		select @var2 = "case when indid = 1 then ' clustered ' end + ' index ' + i.name + ' on ' + " + "'" + rtrim(ltrim(@database)) + "'" 
		select @var3 = " + '..' + o.name + ' (' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,1) + "
		select @var4 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,2) is not null then ', ' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,2) end +"
		select @var5 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,3) is not null then ', ' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,3) end +"
		select @var6 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,4) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,4) end +"
		select @var7 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,5) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,5) end +"
		select @var8 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,6) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,6) end +"
		select @var9 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,7) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,7) end +"
		select @var10 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,8) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,8) end +"
		select @var11 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,9) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,9) end +"
		select @var12 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,10) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,10) end +"
		select @var13 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,11) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,11) end +"
		select @var14 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,12) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,12) end +"
		select @var15 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,13) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,13) end +"
		select @var16 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,14) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,14) end +"
		select @var17 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,15) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,15) end +"
		select @var18 = "case when INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,16) is not null then ',' + INDEX_COL('" + rtrim(ltrim(@database)) + '..' + rtrim(ltrim(@table)) + "',indid,16) end + ') go'"
		select @var19 = 'FROM ' + rtrim(ltrim(@database)) + '..sysindexes i,' + rtrim(ltrim(@database)) + "..sysobjects o WHERE o.name = '" + rtrim(ltrim(@table)) + "' AND o.uid=USER_ID('dbo') AND o.id=i.id and indid between 1 and 250"

		insert into createindexes exec(@var1 + @var2 + @var3 + @var4 + @var5 + @var6 + @var7 + @var8 + @var9 + @var10 + @var11 + @var12 + @var13 + @var14 + @var15 + @var16 + @var17 + @var18 + @var19)

		--Add a go statement to the end for the script file that is generated
		insert into createindexes values ('go')
				
		--Prepare and execute BCP out of create index statements
		select @command = "master..xp_cmdshell 'bcp master..createindexes out e:\sql\createindexes.sql /c /S" + rtrim(ltrim(@sourceserver)) + " /Usa /P" + rtrim(ltrim(@sourcepwd)) + "'"
		exec(@command)
		
		--Truncate create index table
		truncate table createindexes

		--Execute index creation script
		select 'Recreating indexes on table: ' + rtrim(ltrim(@table))
		select @command = "master..xp_cmdshell " + "'" + 'isql -Usa -P' + rtrim(ltrim(@destpwd)) + ' -S' + rtrim(ltrim(@destserver)) + ' -ie:\sql\createindexes.sql' + "'"
		exec (@command)

		--Delete index creation script
		select 'Delete index script for table: ' + ltrim(rtrim(@table))
		select @command = "master..xp_cmdshell 'del e:\sql\createindexes.sql'"
		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.
	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 -P' + rtrim(ltrim(@destpwd)) + ' -S' + rtrim(ltrim(@destserver)) + ' -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.
	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 -P' + rtrim(ltrim(@destpwd)) + ' -S' + rtrim(ltrim(@destserver)) + ' -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 for tables'
drop table #tables
print 'Drop temp table for indexes'
drop table #indexes
print 'Drop temp table for create index statements'
drop table createindexes
select getdate()
set nocount off
return
go
IF OBJECT_ID('dbo.sp__loaddata') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.sp__loaddata >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp__loaddata >>>'
go