In this tutorial, we will teach you how to use SQL Server ALTER SCHEMA to transfer ownership of objects from one schema to another schema with the same database instance.
SQL Server ALTER SCHEMA Introduction
In our previous tutorial, you have learned what a SCHEMA in SQL Server and how to create a SQL Server SCHEMA. Also, you have learned how to create objects inside a schema.
Sometimes a situation arises like to transfer an already created object to another schema within the same database.
The SQL Server ALTER SCHEMA statement allows you to transfer the ownership of objects to another schema with in the same database.
In the subsequent sections, you are going to learn the syntax and example of SQL Server ALTER SCHEMA.
ALTER SCHEMA Syntax
The following shows the syntax of ALTER SCHEMA statement in SQL Server.
ALTER SCHEMA target_schema_name
TRANSFER [ entity_type :: ] object_name;
In this syntax,
ALTER SCHEMA – The keyword used for transferring the ownership of objects from one schema to another.
target_schema_name – The name of the target schema within same database where you want to move the object. Note that you can move any objects in SYS and INFORMATION schema.
TRANSFER – Keyword.
entity_type – This can be an Object, Type or XML schema collection. This defines the class of the entity for which the ownership is being changed.
object_name – The name of the object you want to move.
If you try to move a view, store procedure, function, or trigger, SQL Server will not change the schema name for these objects. So, it is recommended to drop and recreate these objects using the new schema name.
SQL Server also not update the references for these objects automatically. You must change the references of these objects manually in order to reflect the correct one. For example, if you have
on view in SQL Server and one underlying table move to a different schema, you have to manually change the schema name inside that view. Otherwise the view you throw an error if you try to
access it.
ALTER SCHEMA Example
To understand how ALTER SCHEMA works in SQL Server.
First, create table named student in dbo schema.
CREATE TABLE dbo.students
(
roll_no INT PRIMARY KEY IDENTITY,
first_name nvarchar(50) NOT NULL,
last_name nvarchar(50) NOT NULL,
phone_no nvarchar(20),
address nvarchar(255) NOT NULL
);
Then, create a procedure that finds the students by roll_no.
CREATE PROC students_by_id(
@roll_no INT
) AS
BEGIN
SELECT
*
FROM
dbo.students
WHERE
roll_no = @roll_no;
END;
After this, transfer this dbo.students table to university schema.
ALTER SCHEMA university
TRANSFER object::dbo.students;
The above statement will move the students table from dbo schema to university schema.
Now, if you try to access the procedure which you have created above, SQL Server give an error.
EXEC students_by_id 2;
Msg 208, Level 16, State 1, Procedure students_by_id, Line 5 [Batch Start Line 77]
Invalid object name 'dbo.students'.
Finally, to reflect the correct schema name for the students table, you have to modify the procedure manually.
ALTER PROC students_by_id(
@roll_no INT
) AS
BEGIN
SELECT
*
FROM
university.students
WHERE
roll_no = @roll_no;
END;
Lastly if you execute the procedure again, it will give the correct output.
EXEC students_by_id 2;
Summary
In this tutorial, you have learned how to use ALTER SCHEMA to transfer ownership of objects from one schema to another schema with the same database instance.
Then, create a procedure that finds the students by roll_no.
Now, if you try to access the procedure which you have created above, SQL Server give an error.
