ALTER SCHEMA in SQL Server
Last Updated :
02 Jul, 2024
The SQL Server ALTER SCHEMA statement is used to transfer an object (table, view, or function) from one schema to another schema in the same database.
The ALTER SCHEMA statement is used to transfer an 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.
SQL Server ALTER SCHEMA Example
Let's look at an example of ALTER SCHEMA in SQL Server.
First, we will create a table named "geektab" in the dbo schema.
Query:
CREATE TABLE dbo.geektab (
id INT PRIMARY KEY IDENTITY,
name NVARCHAR(40) NOT NULL,
address NVARCHAR(255) NOT NULL
);
INSERT INTO dbo.geektab
(id, name, address)
VALUES
(1, 'Neha', 'B-Wing, Delhi'),
(2, 'Vineet', 'D-Wing, Noida');
Let 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 |
Important Points about ALTER SCHEMA in SQL Server
- The
ALTER SCHEMA
statement is used to transfer objects (e.g., tables, views, functions) from one schema to another within the same database, allowing for better organization and management of database objects. - The
SYS
and INFORMATION_SCHEMA
schemas cannot be altered, as they are reserved for system objects and metadata. - You need
ALTER
permission on the object being transferred and CONTROL
permission on the target schema to use the ALTER SCHEMA
statement. - The
ALTER SCHEMA
operation is transactional, meaning it will roll back if an error occurs during the operation, ensuring database integrity.
Similar Reads
CREATE SCHEMA in SQL Server A schema is a collection of database objects like tables, triggers, stored procedures, etc. A schema is connected with a user which is known as the schema owner. The database may have one or more schema. To create a schema in SQL Server use the 'CREATE SCHEMA' Statement. SQL CREATE SCHEMACREATE SCHE
2 min read
DROP SCHEMA in SQL Server The DROP SCHEMA statement could be used to delete a schema from a database. SQL Server have some built-in schema, for example : dbo, guest, sys, and INFORMATION_SCHEMA which cannot be deleted. Syntax : DROP SCHEMA [IF EXISTS] schema_name; Note : Delete all objects from the schema before dropping the
1 min read
Alter login in SQL Server Alter login statements can be used to changes the properties of a SQL Server login account. Syntax: ALTER LOGIN loginname; GO Enable a disabled login - Syntax : ALTER LOGIN loginname ENABLE; Example - ALTER LOGIN geeks ENABLE; Change password of a login Syntax : ALTER LOGIN geeks WITH PASSWORD = 'ne
1 min read
Create, Alter and Drop schema in MS SQL Server Schema management in MS SQL Server involves creating, altering, and dropping database schema elements such as tables, views, stored procedures, and indexes. It ensures that the database structure is optimized for data storage and retrieval. In this article, we will be discussing schema and how to cr
4 min read
ALTER (RENAME) in SQL In SQL, making structural changes to a database is often necessary. Whether it's renaming a table or a column, adding new columns, or modifying data types, the SQL ALTER TABLE command plays a critical role. This command provides flexibility to manage and adjust database schemas without affecting the
5 min read
How to Rename SQL Server Schema? In SQL, we cannot RENAME a SCHEMA. To achieve this, we need to create a new SCHEMA, transfer all the contents(objects) from the old schema to new schema and then finally delete the old schema using the DROP command. The same is depicted in the below article. For this article, we will be using the Mi
3 min read