Overview
The ALTER SCHEMA statement is a powerful tool used to modify schema information in SQL server. By changing the schema, you can modify the way that objects are accessed and treated, restricting or granting permissions to specific users and groups.
Syntax
1. The Syntax
[USE database_name;]
ALTER SCHEMA target_schema_name TRANSFER securable [(object_name)];
The ‘target_schema_name’ is the name of the destination schema that you intend to transfer the object to. ‘Securable’ is the object you wish to transfer, and ‘Object_name’ is the name of the object in the database.
2. Breakdown of the Syntax
- The USE keyword lets you set the relevant database, which is necessary when working with multiple databases on the same server. However, USE is not required when working with a single database.
- The keyword ALTER SCHEMA allows the modification of the schema of the object that you want to change.
- Target_schema_name is the name of the new schema where the object will reside. All objects in an SQL server should belong in a schema, and if the schema is not specified, the object will be created in the default schema called dbo.
- Securable is the object that you want to modify when using ALTER SCHEMA. A securable refers to any object in the database that has associated permissions, including tables, views, procedures, and user-defined functions.
- Lastly, object_name is the name of the object that you want to alter. It is optional, and if not specified, all objects in the securable will be transferred.
Limitations
While you can use ALTER SCHEMA to modify several objects in the securable, note that some database objects, such as a stored procedure, cannot have their schema changed directly. Instead, it would help if you created a new procedure explicitly in the target schema and redirected any references pointing to the old procedure to the new procedure.
Additionally, not all objects in the securable can be transferred using ALTER SCHEMA. Tables, stored procedures, and synonyms are commonly transferred using ALTER SCHEMA.
Note that VIEWs are securables, but they cannot be directly transferred using ALTER SCHEMA. To transfer a VIEW, you must first drop and recreate the view with the new schema name.
Using ALTER SCHEMA
1. Creating a table
Let’s create a temporary table named ‘temp’ and populate it with some sample data.
CREATE TABLE temp (id INT, name varchar(20));
INSERT INTO temp values (1, 'Mark'), (2, 'Sarah'), (3, 'Steven');Now, let’s transfer the temp table from the default schema to the sales schema:
ALTER SCHEMA sales TRANSFER securable [temp];The temp table is now removed from the dbo schema and moved to the sales schema.
2. Creating a stored procedure
Creating a stored procedure in SQL server can be challenging, but it is possible to transfer it to a new schema.
Here is an example stored procedure that calculates the maximum age from a provided list of ages:
CREATE PROC sales.max_age @age1 INT, @age2 INT AS
SELECT MAX(age) FROM (VALUES (@age1), (@age2)) AS ages(age);The stored procedure now belongs to the sales schema. When you create a new stored procedure, it shows the schema name and the stored procedure name.
3. Transferring a table to a new schema
To transfer tables from one schema to another, you can use the SQL server ALTER SCHEMA statement as follows:
ALTER SCHEMA production.transfer_sales_schema [Sales];When you run this statement, the sales table is transferred from the default production schema to the transfer_sales_schema schema.
4. Manually modifying references
If you want to move an object to a new schema and at the same time maintain existing references, you must update the object references that point to the existing object in the dbo schema. For example, if a stored procedure references tables in a schema, you must modify the stored procedure to reference the tables in the new schema.
Conclusion
In conclusion, the SQL Server ALTER SCHEMA statement is an extremely useful tool that enables you to modify the schema of an object easily. It is a fast and efficient way to transfer tables from one schema to another and to change security permissions that apply to specific databases.
However, the application of ALTER SCHEMA is limited to tables, views, and synonyms; the stored procedures’ schema cannot directly be changed. If you want to change the stored procedures’ schema, you will need to recreate it with the new schema explicitly.
Nevertheless, the ALTER SCHEMA statement is a valuable tool in managing SQL server objects and has a significant role in SQL server management. In summary, SQL Server ALTER SCHEMA statement is a powerful tool that allows you to modify the schema of database objects.
When using this statement, it’s essential to understand its syntax to ensure that it functions adequately. However, not all objects can be transferred via ALTER SCHEMA, and stored procedures cannot have their schema changed directly.
To transfer stored procedures, you must recreate them under the new schema explicitly. Nevertheless, the ALTER SCHEMA statement is a valuable tool in managing SQL server objects, and it plays a crucial role in SQL server management.
Understanding how to use ALTER SCHEMA correctly can help DBAs manage database objects effectively.