ALTER SCHEMA in SQL Server

In SQL Server, ALTER SCHEMA statement is used to transfer an object from one schema to another schema within the same database. This statement can be used to move tables, views, procedures, functions, and other database objects from one schema to another.

The syntax for ALTER SCHEMA statement is as follows:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName;

Where: - NewSchemaName is the name of the schema to which the object will be transferred. - OldSchemaName is the name of the schema from which the object will be transferred. - ObjectName is the name of the object that will be transferred.

For example, let's say we have a table named Customers in the Sales schema and we want to move it to the Marketing schema. We can use the following ALTER SCHEMA statement:

ALTER SCHEMA Marketing TRANSFER Sales.Customers;

This will move the Customers table from the Sales schema to the Marketing schema.

Another method to transfer an object from one schema to another is by using the SQL Server Management Studio (SSMS) GUI. Here are the steps:

• Open SSMS and connect to the SQL Server instance.
• Expand the Databases folder and select the database that contains the object you want to transfer.
• Expand the Security folder and then expand the Schemas folder.
• Right-click on the schema that currently contains the object and select "Transfer Objects to Another Schema".
• In the "Transfer Objects" dialog box, select the object(s) you want to transfer and click the "Add" button.
• In the "Destination Schema" section, select the schema to which you want to transfer the object(s).
• Click the "OK" button to transfer the object(s) to the new schema.

Using SSMS GUI is a more user-friendly way to transfer objects between schemas, especially for those who are not familiar with SQL syntax.