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