• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
sqlserver tutorial

SQL Server Tutorial

SQL Server Tutorial for Beginners

  • HOME
  • START HERE
  • BASICS
  • ADVANCED
    • Index
    • Views
    • Triggers
    • Stored Procedures
    • User-defined Functions
  • FUNCTIONS
Home / SQL Server Basics / SQL Server ALTER SCHEMA

SQL Server ALTER SCHEMA

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
);

Next, we will insert some data into it.

insert into dbo.students values
('Sagar','Sharma', 9523648645, 'Kolkata'),
('Shankar','Kumar', 9523652361, 'New Delhi'),
('Sajal','Roy', 7563145986, 'Hyderabad');
sql server alter schema

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.

sql server alter schema - table schema changed

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.

Was this tutorial helpful?
YesNo
« Previous: SQL Server CREATE SCHEMA
SQL Server DROP SCHEMA :Next »

Primary Sidebar

DATA MANIPULATION

  • SELECT
  • SELECT TOP
  • SELECT DISTINCT
  • OFFSET FETCH
  • ORDER BY
  • GROUP BY
  • BETWEEN
  • LIKE
  • ALIAS
  • HAVING
  • AND
  • OR
  • IN
  • WHERE
  • SELECT INTO
  • INSERT
  • INSERT Multiple rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE CONSTRAINT
  • NOT NULL CONSTRAINT
  • SUBQUERY
  • CORRELATED SUBQUERY
  • JOINS
  • CROSS JOIN
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN
  • SELF JOIN
  • UPDATE Join
  • CASE
  • COALESCE
  • NULL
  • NULLIF
  • UNION
  • INTERSECT
  • MERGE
  • EXCEPT
  • EXISTS
  • GROUPING SET
  • PIVOT
  • ROLLUP
  • CUBE

DATA DEFINITION

  • CREATE DATABASE
  • DROP DATABASE
  • CREATE SCHEMA
  • ALTER SCHEMA
  • DROP SCHEMA
  • CREATE TABLE
  • RENAME TABLE
  • DROP TABLE
  • TRUNCATE TABLE
  • IDENTITY column
  • Sequence
  • ALTER TABLE ADD Column
  • ALTER TABLE ALTER Column
  • ALTER TABLE DROP Column

Footer

About

SQLServerTutorial.org provides free tutorials and guide on SQL Server for Developers, Database Administrators, and Solution Architects who want to get started SQL Server quickly.

Recent Posts

  • SQL Server DROP VIEW
  • SQL Server Indexed View
  • Check view definition
  • SQL Server Rename View
  • SQL Server List Views

Quick Links

  • About
  • Contact Us
  • Privacy Policy
  • SQL Server Index
  • SQL Server Views
  • Terms of Use

Copyright © 2023 www.sqlservertutorial.org. All Rights Reserved.