Link to home
Start Free TrialLog in
Avatar of DBA2010
DBA2010

asked on

SQL DB2005

I have about 200 Store Pro. and 300 Views in my Database. I need to change the Database schema from eds.qqqqq to dbo.qqqqqq inside outside and inside these Sp's and Views.

Is there any code I can use in SQL or any SSIS PCKG I can use?

any advice?
Thanks
Avatar of Humpdy
Humpdy
ASKER CERTIFIED SOLUTION
Avatar of expert_dharam
expert_dharam
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of DBA2010
DBA2010

ASKER

I cant creat the script. I have this error.
System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown. at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e) at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
Avatar of expert_dharam
expert_dharam
Flag of India image
Seems you do not have sufficient RAM to perform this operation.
Make sure you close all the application before you start generating scripts.
Best way to release all the memory resources is by rebooting your system.
Avatar of DBA2010
DBA2010

ASKER

thanks alot
Avatar of Reza Rad
Reza Rad
Flag of New Zealand image
No need to generate scripts,
use this sql statement:

DECLARE @sourceschema VARCHAR(100)

DECLARE @destinationschema VARCHAR(100)

SET @sourceschema = 'sourceschema'

SET @destinationschema = 'destinationschema'

SELECT 'ALTER SCHEMA ' + @destinationschema + ' TRANSFER ' + b.name + '.' + a.name FROM sys.objects a JOIN sys.schemas b

ON a.schema_id=b.schema_id WHERE a.type IN ('U','P','FN','V') AND b.name=@sourceschema

referenece:
http://www.sql-articles.com/scripts/change-transfer-schema-for-all-objects