ALTER SCHEMA in SQL Server

The ALTER SCHEMA statement used to transfer a object from a schema to another schema in the same database.

Syntax :

ALTER SCHEMA target_schema_name    
   TRANSFER [ object_type :: ] object_name;

Parameters :

• target_schema_name is the schema in the database, into which the object will be moved.
• object_type represents the type of the object for which the owner schema will be changed.
• object_name is the name of the object that will be moved to the target_schema_name.

Note : SYS or INFORMATION_SCHEMA cannot be altered.

Example :
Let us create table named geektab in the dbo schema :

CREATE TABLE dbo.geektab
(id INT PRIMARY KEY IDENTITY,
name NVARCHAR(40) NOT NULL,
address NVARCHAR(255) NOT NULL);

Now, insert some rows into the dbo.geektab table :

INSERT INTO dbo.geektab (id, name, address)

VALUES (1, 'Neha', 'B-Wing, Delhi'), (2, 'Vineet', 'D-Wing, Noida');

Lets us create a stored procedure that finds id :

CREATE PROCEDURE sp_get_id(@id INT
) AS
BEGIN
SELECT *
FROM dbo.geektab
WHERE id = @id;
END;

Let us move this dbo.geektab table to the geek schema :

ALTER SCHEMA geek TRANSFER OBJECT::dbo.geektabs;

Run the sp_get_id stored procedure :

EXEC sp_get_id;

SQL Server will throw an error similar to mentioned below :

strong>Msg 208, Level 16, State 1, Procedure sp_get_id, Line 3
Invalid object name 'dbo.geektab'

Now, let us manually alter the stored procedure to reflect the geek schema :

ALTER PROCEDURE sp_get_id( @id INT
) AS
BEGIN SELECT *
FROM geek.geektab
WHERE id = @id;
END;

Run the sp_get_id stored procedure :

EXEC sp_get_id 1; 

Output –

id	name	address
1	Neha	B-Wing, Delhi