schema transfer - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

schema transfer

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-07-28 : 01:56:21

Dear All,
i've so many tables, view and stored procedures created with sa
and somany created with user1

now i'm using the query to transfer all the objests into dbo account.
why because i'm dropping the user(three levels of users i'm developing. one is sa with full powers second is accadmin with create and drop objects and third one is galaxy who can read and write).
alter schema dbo transfer user1.vw_dailyreportsneeded.
now my doubt is there are hundreds of objects. i can not do all these manually with the query. any suggesions to do this?

Arnav
Even you learn 1%, Learn it with 100% confidence.

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-07-28 : 04:39:16

check the link it may solve u r problem

http://weblogs.sqlteam.com/peterl/archive/2008/03/05/Change-schema-for-all-tables.aspx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-28 : 04:47:06

quote:
Originally posted by PeterNeo

check the link it may solve u r problem

http://weblogs.sqlteam.com/peterl/archive/2008/03/05/Change-schema-for-all-tables.aspx

but wont this transfer only tables from schema?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-28 : 05:09:04

ou can use this script and include more types (with UNION ALL) later.
The additional types may be TYPES, SYNONYMS and so on...

SELECT	'ALTER SCHEMA TargetSchema TRANSFER ' + theObjectName
FROM	(
		SELECT	QUOTENAME(routine_schema) + '.' + QUOTENAME(routine_name) AS theObjectName
		FROM	INFORMATION_SCHEMA.ROUTINES
		WHERE	routine_schema <> 'TargetSchema'

		UNION ALL

		SELECT	QUOTENAME(table_schema) + '.' + QUOTENAME(table_name)
		FROM	INFORMATION_SCHEMA.TABLES
		WHERE	table_schema <> 'TargetSchema'
	) AS d

Run the code, copy and paste the result to a query window and run the code.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources