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

Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.

My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.