December 7, 2016 at 1:14 am
hi,
Stored Procedure to transfer all DB objects including data from one schema to another schema
this is the code i tried
ALTER PROC sp_schema_to_schema
@sourceSchema nvarchar(100),
@targetSchema nvarchar(100)
AS
if not exists(select database_id from sys.databases where name =@targetschema)
begin
exec('Create DATABASE'+' '+ @targetschema)
begin
exec('USE'+' '+ @targetschema)
DECLARE c_ALTSCHEMA CURSOR FOR
--SELECT 'ALTER SCHEMA ' + @targetSchema + ' TRANSFER ' + @sourceSchema + '.'+name +';'
--FROM sys.objects
--WHERE type IN ('U','V','P','Fn')
--AND SCHEMA_NAME(SCHEMA_ID) = @sourceSchema
SELECT 'ALTER SCHEMA ' + @targetSchema + ' TRANSFER ' + @sourceSchema + '.'+o.name +';'
FROM sys.objects o
INNER JOIN sys.Schemas s ON o.schema_id = s.schema_id
WHERE s.Name = @sourceSchema
AND (o.Type IN ('U', 'P', 'V'))
DECLARE @SQLStmt NVARCHAR(200)
OPEN c_ALTSCHEMA
FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@SQLStmt)
FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt
END
CLOSE c_ALTSCHEMA
DEALLOCATE c_ALTSCHEMA
END
END
EXEC sp_schema_to_schema AdventureWorks2014, AdventureWorks2019
problem is this only creates the Database named not the tables,data,procedure or view, please help me in this to find the solution
December 7, 2016 at 1:24 am
First a question, can you explain why you want to do this?
😎
There are several ways of doing this, some good and some bad, unfortunately the T-SQL ones are not the best.
December 7, 2016 at 1:51 am
problem, the script only creates the database, it deos not copying the table,data and SP's, please suggest some code changes alternate way to do this.
December 7, 2016 at 2:03 am
GA_SQL (12/7/2016)
problem, the script only creates the database, it deos not copying the table,data and SP's, please suggest some code changes alternate way to do this.
ALTER SCHEMA schema_name TRANSFER transfers a securable between schemas in the current database, it will not copy data between databases.
😎
December 7, 2016 at 2:12 am
Anyways to copy the DB objects with data from one schema to another using stored procedure.
December 7, 2016 at 2:21 am
GA_SQL (12/7/2016)
Anyways to copy the DB objects with data from one schema to another using stored procedure.
You will need to script out all objects in the source db, then create them in the destination db and finally copy the data. Depending on the complexity etc. this can either be easy or rather painful when using T-SQL. Do you have any other options (id. PoSh, SSIS etc.)?
😎
December 7, 2016 at 2:21 am
First, your USE statement only changes the database context for the batch in which it's executed, which, in your case, is only the USE statement itself.
Second, creating a database called NewDB doesn't automatically create a schema called NewDB, so there'll be no new schema to transfer the objects to.
Third, since it's a newly created database, there are no objects to transfer to a different schema.
As Eirikur said, what exactly are you trying to do? It looks as if you are trying to move objects from one database to another, and that the schema is something of a red herring. If we all take a step back from it, we may be able to see a better way of doing it.
John
December 7, 2016 at 2:27 am
Yes i am trying to move all the DB objects from one schema to another schema using SP, using BCP will be right option??
December 7, 2016 at 2:36 am
So you have a schema called AdventureWorks2014 that has objects in it, and you have an empty schema called AdventureWorks2019 that you want to move those objects to. Is that right? What database(s) are those schemas in?
John
December 7, 2016 at 2:47 am
If you are either copying all or majority of the objects, I would suggest doing a backup and restore with a different name, then drop anything that is not needed.
😎
December 7, 2016 at 3:17 am
My objective is to move the contents of one db to another db
December 7, 2016 at 3:23 am
OK, so the schema thing is a red herring. Do you want to move all objects? Do you want to move data, or just create empty tables? Does the target database already exist? Do you want permissions in the target database to be the same as in the source? Sounds like a simple backup and restore might be your best option.
John
December 7, 2016 at 3:25 am
so backup and restore SP will do
December 7, 2016 at 8:05 am
GA_SQL (12/7/2016)
My objective is to move the contents of one db to another db
You can easily use the SSMS Import and Export Wizard for this, and then if it needs to be repeated, choose the option to save process as a SSIS package which you can run on demand.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
December 9, 2016 at 12:22 am
i got the requirement exactly.. i have schema called dbo.table1,dbo.table2 that has to be copied (not transfer) dbonew.table1,dbonew.table2 (with in same db), this below script transfers the name since dbo has been replaced to dbo1, please help me in copy one schema to another schema with in same DB
create PROC sp_schema_to_schema_new
--@sourcedb nvarchar(100),
@sourceSchema nvarchar(100),
@targetSchema nvarchar(100)
AS
if not exists(select database_id from sys.databases where name =@targetschema)
--begin
--exec('USE'+' '+ @sourcedb)
begin
exec('Create SCHEMA'+' '+ @targetschema)
DECLARE c_ALTSCHEMA CURSOR FOR
SELECT 'ALTER SCHEMA ' + @targetSchema + ' COPY'+ @sourceSchema + '.'+name +';'
FROM sys.objects
WHERE type IN ('U','V','P','Fn')
AND SCHEMA_NAME(SCHEMA_ID) = @sourceSchema
DECLARE @SQLStmt NVARCHAR(200)
OPEN c_ALTSCHEMA
FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@SQLStmt)
FETCH NEXT FROM c_ALTSCHEMA INTO @SQLStmt
END
CLOSE c_ALTSCHEMA
DEALLOCATE c_ALTSCHEMA
END
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply