Ravindar, Biztalk Developer and Administrator

Just another BizTalk blog

Change schema name on Tables and Stored procedures in SQL Server 2005

Change Stored procedures in sql 2005

‘Note this is the raw query
SELECT ‘ALTER SCHEMA dbo TRANSFER ‘ + s.Name + ‘.’ + p.Name FROM sys.Procedures p INNER JOIN
sys.Schemas s on p.schema_id = s.schema_id WHERE s.Name = ‘CHANGE_ME_Username’

It would create this kind of output.

* ALTER
* SCHEMA dbo TRANSFER steveschofield.spAuthors1 ALTER SCHEMA dbo TRANSFER steveschofield.spAuthors2
* ALTER SCHEMA dbo TRANSFER steveschofield.spAuthors3

You would run in a new query window, after this refresh SQL Management studio
and the stored procedures would be dbo.spAuthors1, dbo.spAuthors2, dbo.spAuthors3.

Change schema for tables
ALTER SCHEMA NewSchemaname TRANSFER oldschemaname.tablename;

Advertisements

January 19, 2010 - Posted by | Sql Server

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: