Copy One Schema to Another Schema

  • Comments posted to this topic are about the item Copy One Schema to Another Schema

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • 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 Paramenter

    SET @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 XML

    DECLARE @Count INT

    DECLARE @Flag INT

    DECLARE @SchemaName Varchar(100)

    DECLARE @SchemaList Table (ID INT Identity(1,1), SchemaName Varchar(200))

    SET @SqlString = ''

    SET @SchemaName = ''

    SET @Count = 1

    SET @Flag = 0

    SET @XML = N'<root><Schema>' + replace(@CopyToSchema,',','</Schema><Schema>') + '</Schema></root>'

    INSERT INTO @SchemaList

    SELECT 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)

    RETURN

    END

    ---- 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)

    RETURN

    END

    ---- 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 OUTPUT

    IF (@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)

    RETURN

    END

    --- Work Start from Now

    SET @SqlString='

    SET XACT_ABORT ON

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN 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 + 1

    END

    SET @SqlString = @SqlString +'

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    IF @@TRANCOUNT>0 COMMIT TRANSACTION

    SELECT ''[ 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 @SqlString

    EXEC (@SqlString)

    SET NOCOUNT OFF

    GO

  • 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


    Kindest Regards,

    arj

  • 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 @ObjectName

    SET @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 INT

    DECLARE @TotalObject INT

    DECLARE @SchemaCount INT

    DECLARE @ObjectCount INT

    DECLARE @XML XML

    DECLARE @Flag INT

    DECLARE @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 = 1

    SET @ObjectCount = 1

    SET @Flag = 0

    ---- Get All Schema List

    SET @XML = N'<root><Schema>' + replace(@CopyToSchema,',','</Schema><Schema>') + '</Schema></root>'

    INSERT INTO @SchemaList

    SELECT t.value('.','varchar(100)') from @XML.nodes('//root/Schema') as a(t)

    SELECT @TotalSchema = max(ID) from @SchemaList

    ---- Get All Object List

    SET @XML = N'<root><Schema>' + replace(@ObjectName,',','</Schema><Schema>') + '</Schema></root>'

    INSERT INTO @ObjectList

    SELECT t.value('.','varchar(100)') from @XML.nodes('//root/Schema') as a(t)

    SELECT @TotalObject = max(ID) from @ObjectList

    SET @ObjectsWithSchema = ''

    select @ObjectsWithSchema=@ObjectsWithSchema+'Object_ID(''['+@CopyFromSchema+'].'+OBJECTNAME+'''),' from @ObjectList

    select @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)

    RETURN

    END

    ---- 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)

    RETURN

    END

    ---- 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 OUTPUT

    IF (@Flag!=@TotalObject)

    BEGIN

    RAISERROR('SOME OBJECT(S) NOT FOUND. PLEASE CHECK "@ObjectName" Parameter.',16,1)

    RETURN

    END

    --- Work Start from Now

    SET @SqlString='

    SET XACT_ABORT ON

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

    BEGIN 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 + 1

    END

    SET @SqlString = @SqlString +'

    IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION

    IF @@TRANCOUNT>0 COMMIT TRANSACTION

    SELECT ''[ 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 @SqlString

    EXEC (@SqlString)

    SET NOCOUNT OFF

    GO

    ---- xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ----

    /************************************ CREATE OBJECTS SCRIPT ************************************/

    ---- 1. Create schemaes

    CREATE SCHEMA Marketing

    GO

    CREATE SCHEMA Sales

    GO

    CREATE SCHEMA Account

    GO

    CREATE SCHEMA Economics

    GO

    CREATE SCHEMA Employee

    GO

    CREATE SCHEMA Manager

    GO

    ---- 2. Show all schema

    SELECT * FROM sys.schemas WHERE [schema_id]>4 and [schema_id]<16000

    GO

    ---- 3. Create table for every schema

    CREATE 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 Schema

    INSERT 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 procedure

    CREATE PROCEDURE Employee.Proc_1

    AS

    SELECT 'Proc_1',* FROM Employee.SchemaTable

    GO

    CREATE PROCEDURE Employee.Proc_2

    AS

    SELECT 'Proc_2',* FROM Employee.SchemaTable

    GO

    CREATE PROCEDURE Employee.Proc_3

    AS

    SELECT 'Proc_3',* FROM Employee.SchemaTable

    GO

    CREATE PROCEDURE Employee.Proc_4

    AS

    SELECT 'Proc_4',* FROM Employee.SchemaTable

    GO

    ---- 6. Execute the store procedure

    Exec Employee.Proc_1

    GO

    Exec Employee.Proc_2

    GO

    Exec Employee.Proc_3

    GO

    Exec Employee.Proc_4

    GO

    ---- 7. Check the store procedure in different schema

    Select schema_id, schema_name([schema_id]),name,create_date from sys.objects where type='P'

    GO

    /************************************ DROP OBJECTS SCRIPT ************************************/

    ----- 1. Drop Procedure

    DROP PROCEDURE Marketing.Proc_1

    GO

    DROP PROCEDURE Sales.Proc_1

    GO

    DROP PROCEDURE Account.Proc_1

    GO

    DROP PROCEDURE Economics.Proc_1

    GO

    DROP PROCEDURE Employee.Proc_1

    GO

    DROP PROCEDURE Manager.Proc_1

    GO

    DROP PROCEDURE Marketing.Proc_2

    GO

    DROP PROCEDURE Sales.Proc_2

    GO

    DROP PROCEDURE Account.Proc_2

    GO

    DROP PROCEDURE Economics.Proc_2

    GO

    DROP PROCEDURE Employee.Proc_2

    GO

    DROP PROCEDURE Manager.Proc_2

    GO

    DROP PROCEDURE Marketing.Proc_3

    GO

    DROP PROCEDURE Sales.Proc_3

    GO

    DROP PROCEDURE Account.Proc_3

    GO

    DROP PROCEDURE Economics.Proc_3

    GO

    DROP PROCEDURE Employee.Proc_3

    GO

    DROP PROCEDURE Manager.Proc_3

    GO

    DROP PROCEDURE Marketing.Proc_4

    GO

    DROP PROCEDURE Sales.Proc_4

    GO

    DROP PROCEDURE Account.Proc_4

    GO

    DROP PROCEDURE Economics.Proc_4

    GO

    DROP PROCEDURE Employee.Proc_4

    GO

    DROP PROCEDURE Manager.Proc_4

    GO

    ----- 2. Drop Tables

    DROP TABLE Marketing.SchemaTable

    GO

    DROP TABLE Sales.SchemaTable

    GO

    DROP TABLE Account.SchemaTable

    GO

    DROP TABLE Economics.SchemaTable

    GO

    DROP TABLE Employee.SchemaTable

    GO

    DROP TABLE Manager.SchemaTable

    GO

    ----- 3. Drop Schema

    DROP SCHEMA Marketing

    GO

    DROP SCHEMA Sales

    GO

    DROP SCHEMA Account

    GO

    DROP SCHEMA Economics

    GO

    DROP SCHEMA Employee

    GO

    DROP SCHEMA Manager

    GO

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • 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?


    Kindest Regards,

    arj

  • 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 @ObjectName

    SET @CopyFromSchema = 'dbo'

    SET @CopyToSchema=''

    ---------------

    DECLARE @schema NVARCHAR(50),@schema_id int

    DECLARE x CURSOR for

    SELECT 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 x

    FETCH NEXT FROM x INTO @schema,@schema_id

    WHILE @@FETCH_STATUS =0

    BEGIN

    IF LEN (@CopyToSchema) =0

    SET @CopyToSchema=@schema

    ELSE

    SET @CopyToSchema=@CopyToSchema + ',' + @schema

    PRINT @CopyToSchema

    FETCH NEXT FROM x INTO @schema,@schema_id

    END

    CLOSE x

    DEALLOCATE x

    Att.

    Disney :w00t:

  • Thanks Disney for your valuable comments.

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • Thanks for the script Danny and others for input.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply