Step 1>
Just run given stored procedure in that database for which you want to create a dummy or backup
create proc mypro_for_copy_database
@newdbname varchar(100),
@olddbname varchar(100)
as
begin
declare @sq nvarchar(max)
set @sq='create database '+@newdbname
exec sp_executesql @sq
declare @s nvarchar(100)
set @s='use '+@olddbname
exec sp_executesql @s
declare @oldname varchar(100)
declare @sql nvarchar(max)
declare create_database cursor for select name from sys.tables
open create_database
fetch next from create_database into @oldname
while @@FETCH_STATUS=0
begin
set @sql='use '+@olddbname+' select * into '+@newdbname+'.dbo.'+@oldname+' from '+@olddbname+'.dbo.'+@oldname+''
exec sp_executesql @sql
fetch next from create_database into @oldname
end
close create_database
deallocate create_database
end
step 2> now run this query
exec mypro_for_copy_database 'newdatabase','currentdatabse'
Note---do not create manually new databse..this stored procedure automatically will create a database as named you give in query..
Step 3> Enjoy, Your Task completed without a complex query.