>I want to be able to bulk copy all the tables in the database and rename them at the same time. I currently have code that finds all the tables in a database, loops through and does this:
>
>EXECUTE ('SELECT * INTO x_' + @tablename + ' FROM ' + @tablename)
>
>Whilst this works ok, I have 2 problems.
>
>1) Takes a long time
>2) Fills up transaction logs.
>
>Is there a way that could do it quicker and/or not do any logging?
>
>I also want to copy all the tables in 1 database into a newly created database but rename the tables at the same time. Is this possible using Stored Procedures?
>
>Many Thanks

Based on MS sp_MsForEachTable:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
declare @mscat nvarchar(12)
select @mscat = ltrim(str(convert(int, 0x0002)))

/* Create the select */
exec(N'declare hCForEach cursor global for select object_name(id) from dbo.sysobjects o '
         + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 ')

exec sp_MSforeach_worker N'SELECT *  INTO x_? from ?', N'?', '', ''

But I am not sure that this will be faster, and also not sure that this operation is not logged, but you could try.