SQLServerCentral / Article Discussions / Discuss content posted by vinaykumar / Article Discussions by Author / Copy One Schema to Another Schema / Latest Posts

RE: Copy One Schema to Another Schema

Danny Ocean — Sun, 11 Dec 2011 22:40:59 GMT

Thanks Disney for your valuable comments.

RE: Copy One Schema to Another Schema

Disney Hammerschmidt — Thu, 08 Dec 2011 11:14:44 GMT

A small change.. cursor for obtain all schemas and set it to a variable used in method:SET NOCOUNT ON DECLARE @CopyFromSchema Varchar(50)DECLARE @CopyToSchema Varchar(200)DECLARE @ObjectName Varchar(100)---- Set The Paramenter---- Now you can pass multiple parameters in @ObjectNameSET @CopyFromSchema = 'dbo'SET @CopyToSchema=''---------------DECLARE @schema NVARCHAR(50),@schema_id intDECLARE x CURSOR forSELECT name,schema_id FROM sys.schemas --WHERE name NOT LIKE 'db%' AND name NOT LIKE 'sys' AND name NOT LIKE 'inf%' AND name NOT LIKE 'D%' AND name NOT LIKE 'ad%' OPEN xFETCH NEXT FROM x INTO @schema,@schema_idWHILE @@FETCH_STATUS =0BEGINIF LEN (@CopyToSchema) =0SET @CopyToSchema=@schema ELSESET @CopyToSchema=@CopyToSchema + ',' + @schema PRINT @CopyToSchemaFETCH NEXT FROM x INTO @schema,@schema_idENDCLOSE xDEALLOCATE xAtt.Disney :w00t:

RE: Copy One Schema to Another Schema

arj — Fri, 09 Sep 2011 01:01:15 GMT

Hello Vinay,Simply awesome.It's working for SP's.I have tried (changed a bit) using this script for creating table of one schema to multiple schemas.But not worked. Can we achieve above functionality from this script?Creating one\Multiple table of one schema to multiple schemas? can we achieve this?

RE: Copy One Schema to Another Schema

Danny Ocean — Wed, 07 Sep 2011 05:29:22 GMT

Hi Arjun,I have edited script for your requirement. Check it./****************************************************************************************************************** Script Name :: Copy Store Procedure(s) From one to Multiple Schema Created on :: Sept 07, 2011 Created By :: Vinay Kumar Description :: This script will copy Given no of store procedure(s) from a one schema to another schema. IF object is Already Exists, then first it will drop and then recreate it.******************************************************************************************************************/SET NOCOUNT ON DECLARE @CopyFromSchema Varchar(50)DECLARE @CopyToSchema Varchar(200)DECLARE @ObjectName Varchar(100)---- Set The Paramenter---- Now you can pass multiple parameters in @ObjectNameSET @CopyFromSchema = 'Employee'SET @CopyToSchema = 'Marketing,Sales,Account,Economics,Employee,Manager'SET @ObjectName = 'Proc_1,Proc_2,Proc_3,Proc_4'DECLARE @SqlString NVarchar(max)DECLARE @TotalSchema INTDECLARE @TotalObject INT DECLARE @SchemaCount INTDECLARE @ObjectCount INTDECLARE @XML XMLDECLARE @Flag INTDECLARE @SchemaName Varchar(100)DECLARE @CurrentObjectName NVarchar(max)DECLARE @ObjectsWithSchema NVarchar(max)DECLARE @SchemaList Table (ID INT Identity(1,1), SchemaName Varchar(200))DECLARE @ObjectList Table (ID INT Identity(1,1), ObjectName Varchar(200))SET @SqlString = ''SET @SchemaName = ''SET @SchemaCount = 1SET @ObjectCount = 1SET @Flag = 0---- Get All Schema ListSET @XML = N'<root><Schema>' + replace(@CopyToSchema,',','</Schema><Schema>') + '</Schema></root>'INSERT INTO @SchemaListSELECT t.value('.','varchar(100)') from @XML.nodes('//root/Schema') as a(t)SELECT @TotalSchema = max(ID) from @SchemaList---- Get All Object ListSET @XML = N'<root><Schema>' + replace(@ObjectName,',','</Schema><Schema>') + '</Schema></root>'INSERT INTO @ObjectListSELECT t.value('.','varchar(100)') from @XML.nodes('//root/Schema') as a(t)SELECT @TotalObject = max(ID) from @ObjectListSET @ObjectsWithSchema = ''select @ObjectsWithSchema=@ObjectsWithSchema+'Object_ID(''['+@CopyFromSchema+'].'+OBJECTNAME+'''),' from @ObjectListselect @ObjectsWithSchema=SUBSTRING(@ObjectsWithSchema,1,LEN(@ObjectsWithSchema)-1)---- Check @CopyFromSchema Variable IF NOT EXISTS ( SELECT 1 FROM sys.schemas WHERE NAME = @CopyFromSchema)BEGIN RAISERROR ('SCHEMA NOT FOUND. PLEASE CHECK "@CopyFromSchema" Parameter.',16,1) RETURNEND---- Check @CopyToSchema Variable IF ((SELECT COUNT(*) FROM sys.schemas WHERE name IN (SELECT SchemaName FROM @SchemaList))!=@TotalSchema)BEGIN RAISERROR ('ONE OR MORE SCHEMA NOT FOUND. PLEASE CHECK "@CopyToSchema" Parameter.',16,1) RETURNEND---- Check @ObjectName Variable SET @SqlString = 'SELECT @Flag_OUT = Count(*) FROM sys.objects WHERE [object_id] IN ('+@ObjectsWithSchema+')'EXEC Sp_executesql @SqlString,N'@Flag_OUT int OUTPUT',@Flag_OUT = @Flag OUTPUTIF (@Flag!=@TotalObject)BEGIN RAISERROR('SOME OBJECT(S) NOT FOUND. PLEASE CHECK "@ObjectName" Parameter.',16,1) RETURNEND--- Work Start from NowSET @SqlString='SET XACT_ABORT ONSET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSACTION'WHILE (@TotalSchema>=@SchemaCount)BEGIN SELECT @SchemaName = SchemaName FROM @SchemaList WHERE ID = (@SchemaCount) IF (@SchemaCount=1) BEGIN SET @SqlString = @SqlString+' SELECT ''[ BEFORE ]'',Schema_ID,SCHEMA_NAME(Schema_ID) AS ''Schema_Name'',Name,create_date,modify_date FROM SYS.OBJECTS WHERE Name IN ('''+REPLACE(@ObjectName,',',''',''')+''') ORDER BY CREATE_DATE DESC, MODIFY_DATE DESC DECLARE @SQL nvarchar(max) ' END SET @ObjectCount = 1 WHILE (@TotalObject>=@ObjectCount) BEGIN SELECT @CurrentObjectName = ObjectName FROM @ObjectList WHERE ID = (@ObjectCount) SET @SqlString=@SqlString+' IF EXISTS (SELECT 1 FROM sys.Objects WHERE NAME ='''+@CurrentObjectName+''' AND SCHEMA_ID= SCHEMA_ID('''+@SchemaName+''')) BEGIN DROP PROCEDURE ['+@SchemaName+'].['+@CurrentObjectName+'] END' SET @SqlString=@SqlString+' SELECT @SQL = REPLACE(REPLACE(OBJECT_DEFINITION (object_id('''+@CopyFromSchema+'.'+@CurrentObjectName+''')),''['+@CopyFromSchema+'].'',''['+@SchemaName+'].''),'''+@CopyFromSchema+'.'','''+@SchemaName+'.'') exec sp_executeSQL @SQL ' SET @ObjectCount = @ObjectCount + 1 END SET @SchemaCount = @SchemaCount + 1ENDSET @SqlString = @SqlString +'IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTIONIF @@TRANCOUNT>0 COMMIT TRANSACTIONSELECT ''[ AFTER ]'',Schema_ID,SCHEMA_NAME(Schema_ID) AS ''Schema_Name'',Name,create_date,modify_date FROM SYS.OBJECTS WHERE Name in ('''+REPLACE(@ObjectName,',',''',''')+''') ORDER BY CREATE_DATE DESC, MODIFY_DATE DESC'--PRINT @SqlStringEXEC (@SqlString)SET NOCOUNT OFFGO ---- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ----/************************************ CREATE OBJECTS SCRIPT ************************************/---- 1. Create schemaesCREATE SCHEMA MarketingGOCREATE SCHEMA SalesGOCREATE SCHEMA AccountGOCREATE SCHEMA EconomicsGOCREATE SCHEMA EmployeeGOCREATE SCHEMA ManagerGO---- 2. Show all schemaSELECT * FROM sys.schemas WHERE [schema_id]>4 and [schema_id]<16000GO---- 3. Create table for every schemaCREATE TABLE Marketing.SchemaTable (name varchar(max))CREATE TABLE Sales.SchemaTable (name varchar(max))CREATE TABLE Account.SchemaTable (name varchar(max))CREATE TABLE Economics.SchemaTable (name varchar(max))CREATE TABLE Employee.SchemaTable (name varchar(max))CREATE TABLE Manager.SchemaTable (name varchar(max))GO---- 4. Insert data for Every SchemaINSERT INTO Marketing.SchemaTable values ('Marketing Schema')INSERT INTO Sales.SchemaTable values ('Sales Schema')INSERT INTO Account.SchemaTable values ('Account Schema')INSERT INTO Economics.SchemaTable values ('Economics Schema')INSERT INTO Employee.SchemaTable values ('Employee Schema')INSERT INTO Manager.SchemaTable values ('Manager Schema')GO---- 5. Create a test store procedureCREATE PROCEDURE Employee.Proc_1ASSELECT 'Proc_1',* FROM Employee.SchemaTableGOCREATE PROCEDURE Employee.Proc_2ASSELECT 'Proc_2',* FROM Employee.SchemaTableGOCREATE PROCEDURE Employee.Proc_3ASSELECT 'Proc_3',* FROM Employee.SchemaTableGOCREATE PROCEDURE Employee.Proc_4ASSELECT 'Proc_4',* FROM Employee.SchemaTableGO---- 6. Execute the store procedureExec Employee.Proc_1GOExec Employee.Proc_2GOExec Employee.Proc_3GOExec Employee.Proc_4GO---- 7. Check the store procedure in different schemaSelect schema_id, schema_name([schema_id]),name,create_date from sys.objects where type='P'GO/************************************ DROP OBJECTS SCRIPT ************************************/----- 1. Drop ProcedureDROP PROCEDURE Marketing.Proc_1GODROP PROCEDURE Sales.Proc_1GODROP PROCEDURE Account.Proc_1GODROP PROCEDURE Economics.Proc_1GODROP PROCEDURE Employee.Proc_1GODROP PROCEDURE Manager.Proc_1GODROP PROCEDURE Marketing.Proc_2GODROP PROCEDURE Sales.Proc_2GODROP PROCEDURE Account.Proc_2GODROP PROCEDURE Economics.Proc_2GODROP PROCEDURE Employee.Proc_2GODROP PROCEDURE Manager.Proc_2GODROP PROCEDURE Marketing.Proc_3GODROP PROCEDURE Sales.Proc_3GODROP PROCEDURE Account.Proc_3GODROP PROCEDURE Economics.Proc_3GODROP PROCEDURE Employee.Proc_3GODROP PROCEDURE Manager.Proc_3GODROP PROCEDURE Marketing.Proc_4GODROP PROCEDURE Sales.Proc_4GODROP PROCEDURE Account.Proc_4GODROP PROCEDURE Economics.Proc_4GODROP PROCEDURE Employee.Proc_4GODROP PROCEDURE Manager.Proc_4GO----- 2. Drop TablesDROP TABLE Marketing.SchemaTableGODROP TABLE Sales.SchemaTableGODROP TABLE Account.SchemaTableGODROP TABLE Economics.SchemaTableGODROP TABLE Employee.SchemaTableGODROP TABLE Manager.SchemaTableGO----- 3. Drop SchemaDROP SCHEMA MarketingGODROP SCHEMA SalesGODROP SCHEMA AccountGODROP SCHEMA EconomicsGODROP SCHEMA EmployeeGODROP SCHEMA ManagerGO

RE: Copy One Schema to Another Schema

arj — Wed, 07 Sep 2011 01:12:02 GMT

Hello Vinay,It's a nice script. Quite useful to me.How to achieve this functionality from your script.?Rather than copying one object from one schema can we copy multiple objects from the same schema to multiple schema.? Example: SET @CopyFromSchema = 'SchemaName'SET @CopyToSchema = 'schema1,schema2,schema3'SET @ObjectName = 'object1,object2,object3' -- Want to achieve this..Regards,Arjun

RE: Copy One Schema to Another Schema

minehere — Wed, 31 Aug 2011 07:16:02 GMT

Just a few things.1. The script stops coping when the copy from schema comes up as the target, so as written it you would end up with the stored proceure in Marketing, Sales, Account and Economics and not in Employee and manager.The easiest correction is to test for @SchemaName not being @CopyFromSchema before doing the drop and create.2. I would make a distinction between stored procedure not found in any schema versus not in the copy from schema specified.3. I would put out the values that are not found in the error message.With these changes I have/****************************************************************************************************************** Script Name :: Copy SP From one to Multiple Schema Created on :: June 27, 2011 Created By :: Vinay Kumar Description :: This script will copy a store procedure from a one schema to another schema. IF object is Already Exists, then first it will drop and then recreate it.******************************************************************************************************************/SET NOCOUNT ON DECLARE @CopyFromSchema Varchar(50)DECLARE @CopyToSchema Varchar(200)DECLARE @ObjectName Varchar(100)---- Set The ParamenterSET @CopyFromSchema = 'Employee'SET @CopyToSchema = 'Marketing,Sales,Account,Economics,Employee,Manager'SET @ObjectName = 'ProcInAll'DECLARE @ErrorStr varchar(120)DECLARE @SqlString NVarchar(max)DECLARE @TotalSchema INT DECLARE @XML XMLDECLARE @Count INTDECLARE @Flag INTDECLARE @SchemaName Varchar(100)DECLARE @SchemaList Table (ID INT Identity(1,1), SchemaName Varchar(200))SET @SqlString = ''SET @SchemaName = ''SET @Count = 1SET @Flag = 0SET @XML = N'<root><Schema>' + replace(@CopyToSchema,',','</Schema><Schema>') + '</Schema></root>'INSERT INTO @SchemaListSELECT t.value('.','varchar(100)') from @XML.nodes('//root/Schema') as a(t)SELECT @TotalSchema = max(ID) from @SchemaList---- Check @CopyFromSchema Variable IF NOT EXISTS ( SELECT 1 FROM sys.schemas WHERE NAME = @CopyFromSchema)BEGIN set @ErrorStr = 'SCHEMA ' + @CopyFromSchema+ ' NOT FOUND. PLEASE CHECK "@CopyFromSchema" Parameter.' RAISERROR(@ErrorStr,16,1) RETURNEND---- Check @CopyToSchema Variable IF ((SELECT COUNT(*) FROM sys.schemas WHERE name IN (SELECT SchemaName FROM @SchemaList))!=@TotalSchema)BEGIN set @ErrorStr = 'ONE OR MORE SCHEMA NOT FOUND. PLEASE CHECK "@CopyToSchema" Parameter.' RAISERROR(@ErrorStr,16,1) RETURNEND---- Check @ObjectName Variable SET @SqlString = 'SELECT @Flag_OUT = Count(*) FROM sys.objects WHERE [object_id]=Object_ID('''+@CopyFromSchema+'.'+@ObjectName+''')'EXEC Sp_executesql @SqlString,N'@Flag_OUT int OUTPUT',@Flag_OUT = @Flag OUTPUTIF (@Flag!=1)BEGIN select @Flag = COUNT(*) from sys.objects where name = '' + @ObjectName + '' IF (@Flag!=0) set @ErrorStr = 'OBJECT '+ @ObjectName + ' NOT FOUND IN SCHEMA ' + @CopyFromSchema + '. PLEASE CHECK "@ObjectName" Parameter.' ELSE set @ErrorStr = 'OBJECT '+ @ObjectName + ' NOT FOUND IN DATABASE. PLEASE CHECK "@ObjectName" Parameter.' RAISERROR(@ErrorStr,16,1) RETURNEND--- Work Start from NowSET @SqlString='SET XACT_ABORT ONSET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSACTION'WHILE (@TotalSchema>=@Count)BEGIN SELECT @SchemaName = SchemaName FROM @SchemaList WHERE ID = (@Count) IF (@Count=1) BEGIN SET @SqlString = @SqlString+' SELECT ''[ BEFORE ]'',Schema_ID,SCHEMA_NAME(Schema_ID) AS ''Schema_Name'',Name,create_date,modify_date FROM SYS.OBJECTS WHERE Name = '''+@ObjectName+''' ORDER BY CREATE_DATE DESC, MODIFY_DATE DESC DECLARE @SQL nvarchar(max) ' END if @SchemaName <> @CopyFromSchema BEGIN SET @SqlString=@SqlString+' -------- FOR ['+@SchemaName+'] IF EXISTS (SELECT 1 FROM sys.Objects WHERE NAME ='''+@ObjectName+''' AND SCHEMA_ID= SCHEMA_ID('''+@SchemaName+''')) BEGIN DROP PROCEDURE ['+@SchemaName+'].['+@ObjectName+'] END' SET @SqlString=@SqlString+' SELECT @SQL = REPLACE(REPLACE(OBJECT_DEFINITION (object_id('''+@CopyFromSchema+'.'+@ObjectName+''')),''['+@CopyFromSchema+'].'',''['+@SchemaName+'].''),'''+@CopyFromSchema+'.'','''+@SchemaName+'.'') exec sp_executeSQL @SQL ' END SET @Count = @Count + 1ENDSET @SqlString = @SqlString +'IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTIONIF @@TRANCOUNT>0 COMMIT TRANSACTIONSELECT ''[ AFTER ]'',Schema_ID,SCHEMA_NAME(Schema_ID) AS ''Schema_Name'',Name,create_date,modify_date FROM SYS.OBJECTS WHERE Name = '''+@ObjectName+''' ORDER BY CREATE_DATE DESC, MODIFY_DATE DESC'--PRINT @SqlStringEXEC (@SqlString)SET NOCOUNT OFFGO

Copy One Schema to Another Schema

Danny Ocean — Wed, 31 Aug 2011 04:29:28 GMT

Comments posted to this topic are about the item [B]Copy One Schema to Another Schema[/B]