Open In App
Related Articles

ALTER SCHEMA in SQL Server

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report
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

Last Updated : 04 Sep, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads