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 |
Last Updated :
04 Sep, 2020
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...