How to change the database object schemas in Sql Server.?

To change the database object schema in MS-SQL Server

Step 1:

1.     Open Microsoft SQL Server Management Studio and log in.

2.     Click the New Query button.

3.     Select Database from Toolbar.

4.     Paste the following script into the New Query box changing to old-schema the name of the current schema: 

SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name
FROM sys.Objects o
INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
WHERE s.Name = 'old-schema'
And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V') 

5.     Click Execute

This will collects 'old-schema' related objects from database generate this query will result in output in the Results box which looks similar to the output below:

ALTER SCHEMA dbo TRANSFER old-schema.Table1

ALTER SCHEMA dbo TRANSFER old-schema.Table2
ALTER SCHEMA dbo TRANSFER old-schema.Procedure1

ALTER SCHEMA dbo TRANSFER old-schema.Procedure2
ALTER SCHEMA dbo TRANSFER old-schema.View1

ALTER SCHEMA dbo TRANSFER old-schema.View2

ALTER SCHEMA dbo TRANSFER old-schema.View3

Step 2:

1.     Click the New Query button.

2.     Paste the queries from the output of Part 1 into your new Query box

3.     Click Execute

It will transfer the schema name across to the new schema, also changing the stored procedures and views.