Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Copy One Schema to Another Schema Expand / Collapse
Author
Message
Posted Wednesday, August 31, 2011 4:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Comments posted to this topic are about the item Copy One Schema to Another Schema



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1167988
Posted Wednesday, August 31, 2011 7:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 11:20 AM
Points: 21, Visits: 81
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
Post #1168074
Posted Wednesday, September 7, 2011 1:12 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 13, 2012 9:46 PM
Points: 14, Visits: 240
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
Post #1170859
Posted Wednesday, September 7, 2011 5:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546

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 !!!
www.GrowWithSql.com

Post #1170975
Posted Friday, September 9, 2011 1:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 13, 2012 9:46 PM
Points: 14, Visits: 240
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
Post #1172283
Posted Thursday, December 8, 2011 11:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 4, 2013 1:27 PM
Points: 10, Visits: 117
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
Post #1218814
Posted Sunday, December 11, 2011 10:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 6, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Thanks Disney for your valuable comments.



Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
www.GrowWithSql.com

Post #1219989
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse