Step 1. Let's say if you have two SQL Server databases, olddbname and newdbname. The following script creates a stored procedure that will copy all tables and their data from olddbname to newdbname.
  1. create proc mypro_for_copy_database
  2. @newdbname varchar(100),
  3. @olddbname varchar(100)
  4. as
  5. begin
  6. declare @sq nvarchar(max)
  7. set @sq='create database '+@newdbname
  8. exec sp_executesql @sq
  9. declare @s nvarchar(100)
  10. set @s='use '+@olddbname
  11. exec sp_executesql @s
  12. declare @oldname varchar(100)
  13. declare @sql nvarchar(max)
  14. declare create_database cursor for select name from sys.tables
  15. open create_database
  16. fetch next from create_database into @oldname
  17. while @@FETCH_STATUS=0
  18. begin
  19. set @sql='use '+@olddbname+' select * into '+@newdbname+'.dbo.'+@oldname+' from '+@olddbname+'.dbo.'+@oldname+''
  20. exec sp_executesql @sql
  21. fetch next from create_database into @oldname
  22. end
  23. close create_database
  24. deallocate create_database
  25. end
If you don't want to create this as a SP, you can run this query direct.
Step 2. Execute SP by using this query. Here you pass your two databases names.
  1. exec mypro_for_copy_database 'newdatabase','currentdatabse'
Note: Do not create new databse manually. This stored procedure will automatically create a new database with the name you give in the query.
Step 3. Enjoy. Your task is completed without a complex query.
Here are two recommended detailed tutorials:
  1. Copy Tables Data from One Database to Another In SQL Server
  2. Copy Table Schema and Data from One SQL Server Database to Another SQL Server Database