Copy Multiple objects from One Schema to Another Schema

Question

Copy Multiple objects from One Schema to Another Schema

Vinay Kumar

SSCertifiable

Points: 6099
More actions
September 29, 2011 at 11:52 pm

#259394

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

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mehernosh SSC Eights! Points: 826 More actions · Answer 1

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.

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 2

Mehernosh, Thank for your suggestion !!! 🙂

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

Arju SSC Veteran Points: 243 More actions · Answer 3

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

Vinay Kumar SSCertifiable Points: 6099 More actions · Answer 4

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 5

akljfhnlaflkj

SSC Guru

Points: 76202

May 12, 2016 at 7:10 am

#1878571

Another good script Danny, thanks.