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 |
Contact Us