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 Multiple objects from One Schema to Another Schema Expand / Collapse
Author
Message
Posted Thursday, September 29, 2011 11:52 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

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



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

Post #1183537
Posted Tuesday, October 04, 2011 12:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, January 16, 2014 7:51 AM
Points: 40, Visits: 218
Hi Vinay, nice script.
Liked both your scripts for objects copying across schemas. Just wanted to share this other way to add SQL objects to multiple database on a server.

http://www.sqlservercentral.com/Forums/Topic1168030-9-1.aspx

If you wanted to add a SQL table or view or any SQL object to multiple database on your server and do not want to execute the script over and over again in all the multiple databases than the following SQL script can be used.

There have been instances where I had to add a stored procedure or a table in multiple client database as a part of the schema change process and this script has come in handy.

--ADD SQL OBJECTS(TABLES, VIEWS, SPS, UDFS ET AL) TO ALL THE DATABASES ON THE SERVER. 

PRINT '##### BEGIN DB SCRIPT ##### '
EXEC SP_MSFOREACHDB
'USE [?]IF ''?'' NOT IN (''MASTER'', ''MODEL'', ''MSDB'', ''TEMPDB'', ''ADVENTUREWORKS'' )
BEGIN
IF NOT EXISTS
(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''MYTABLE'')
BEGIN
CREATE TABLE [DBO].[MYTABLE](COLUMN_1 VARCHAR(50) NULL, COLUMN_2 DATETIME NULL)
PRINT '' TABLE CREATED IN DATABASE ('' + DB_NAME() + '')''
END
ELSE
PRINT '' TABLE PRESENT IN DATABASE ('' + DB_NAME() + '')''
END '
PRINT '##### DB SCRIPT COMPLETED #####'




Regards,


Mehernosh.
Post #1185041
Posted Tuesday, October 04, 2011 2:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, February 06, 2014 4:15 AM
Points: 1,242, Visits: 1,546
Mehernosh, Thank for your suggestion !!!



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

Post #1185067
Posted Wednesday, April 04, 2012 6:55 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 12:05 AM
Points: 6, Visits: 49
Really it's a nice and useful articles.

Practically it solved my issue and saved my time.

I have a question again... Can we move one table \ multiple tables from one schema to multiple schemas along with all the properties (identiy column, default values, primary key, Cluster and non-cluster indexes)

Again this will save my time lot.

Thanks,
Mallikarjun
Post #1278499
Posted Thursday, March 28, 2013 11:34 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

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

Sorry for reply to0 late. Please check the below link. this will help you.

http://www.sqlservercentral.com/scripts/T-SQL/97571/

I have also create different kind of scripts related to copy objects from one schema to another schema.
You can check my other scripts.




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

Post #1436830
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse