Copy All Tables From One Database To Another In SQL Server

Here is a SQL query to copy all tables from a SQL Server database to another SQL Server database including tables data.

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: