Copy One Schema to Another Schema

,

Hi,

Currently, I am working on a project where we have more than 10 clients. We manage all clients through different schema. Every schema object is accessing only its own schema objects and not other schema objects.

Sometimes I need to update some stored procedures which are the same in all schema. In this case, I have manually opened and udpated the stored procedure for every schema. I googled for this, but didn't find anything. So, I created this script.

Here I am creating a testing scenario. In this, I create a table: "SchemaTable" in every schema (Marketing, Sales ,Accounts ,Economics, Employee, Manager, Employee). Then I create a stored procedure only in Employee schema. This schema is accessing only same schema table.

Now I want to create this store procedure in all schema, then execute the main script.

Note :- Before executing the script, please check the parameters and follow these steps:

1. Create an object for Testing. Execute "CREATE OBJECTS SCRIPT" from script section.

2. Change the pameter value:

  @CopyFromSchema = Enter the schema name, where you want to pick the definition.

  @CopyToSchema = Enter the schema name in which you want to create or update the object.

  @ObjectName = Enter the object that you want to copy.

3. Drop object, Execute "DROP OBJECTS SCRIPT" from script section.

I feel that maybe someone is suffering like me, so I am sharing this.

Feel free to comment if you have any suggestions for this.

Thanks.

/******************************************************************************************************************
	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 = 'GetExchangeBenefitDetails'



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
	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]=Object_ID('''+@CopyFromSchema+'.'+@ObjectName+''')'
EXEC Sp_executesql @SqlString,N'@Flag_OUT int OUTPUT',@Flag_OUT = @Flag OUTPUT


IF (@Flag!=1)
BEGIN
	RAISERROR('OBJECT 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>=@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
	
	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
	'
	
	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    




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


/************************************ 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.ProcInAll
AS
SELECT * FROM Employee.SchemaTable
GO

---- 6. Execute the store procedure
Exec Employee.ProcInAll
GO

---- 7. Check the store procedure in different schema
Select schema_id, schema_name([schema_id]),name,create_date from sys.objects where name ='ProcInAll'
GO



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

----- 1. Drop Procedure
DROP PROCEDURE Marketing.ProcInAll
GO
DROP PROCEDURE Sales.ProcInAll
GO
DROP PROCEDURE Account.ProcInAll
GO
DROP PROCEDURE Economics.ProcInAll
GO
DROP PROCEDURE Employee.ProcInAll
GO
DROP PROCEDURE Manager.ProcInAll
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

Rate

5 (1)

Share

Share

Rate

5 (1)