¿Cómo modificar Schema en SQL Server con ALTER SCHEMA?

Resumen: en este tutorial, aprenderás a usar ALTER SCHEMA de SQL Server para cambia la definición de un esquema existente. Utiliza este comando para cambiar un objeto de un esquema a otro.

¿Que es SCHEMA de SQL Server?

En SQL Server los esquemas representan un conjunto lógico dentro de una base de datos. Permiten organizar mejor de manera lógica las tablas, vistas, procedimientos y funciones. Por defecto, durante la creación de un objeto, éste se registra en el esquema del usuario actual. Por lo tanto, el esquema tiene el mismo nombre que el del usuario. Es posible asociar un esquema existente a un usuario, o bien crear un objeto en un esquema diferente al que tiene asociado el usuario

Durante la creación de la base de datos existe el esquema dbo. Este esquema está presente en todas las bases de datos. En el arículo anterior vimos ¿Cómo crear Schema en SQL Server?.

Introducción ALTER SCHEMA de SQL Server

La instrucción ALTER SCHEMA permite transferir un objeto de un esquema a otro dentro de la misma base de datos.

Ten en cuenta que un objeto es un recurso al que el sistema de autorización del motor de base de datos controla el acceso. Por ejemplo, una tabla es un objeto.

A continuación se muestra la sintaxis de la instrucción ALTER SCHEMA:

ALTER SCHEMA target_schema_name   
    TRANSFER [ entity_type :: ] object_name;

En esta sintaxis se observa los siguiente:

• target_schema_name es el nombre de un esquema en la base de datos actual, a la que desea mover un objeto. Ten en cuenta que no puede ser SYS ni INFORMATION_SCHEMA.
• El entity_type puede ser objeto, tipo o colección de esquemas XML. Por defecto es Objeto. El entity_type representa la clase de la entidad para la que se cambia el propietario.
• object_name es el nombre del objeto que deseas mover a target_schema_name.

Si mueves un procedimiento almacenado, una función, una vista o un trigger, SQL Server no cambiará el nombre de esquema de estos elementos. Por lo tanto, se recomienda eliminar y volver a crear estos objetos en el nuevo esquema en lugar de utilizar la declaración ALTER SCHEMA para moverlos.

Si mueves un objeto, por ejemplo, una tabla, SQL Server no actualizará las referencias de estos objetos automáticamente. Debes modificar manualmente las referencias para reflejar el nuevo nombre de esquema. Por ejemplo, si mueves una tabla a la que se hace referencia en un procedimiento almacenado, debe modificar el procedimiento almacenado para reflejar el nuevo nombre de esquema.

Ejemplo de ALTER SCHEMA de SQL Server

Primero, crea una nueva tabla llamada offices en el esquema dbo:

CREATE TABLE dbo.offices
(
    office_id      INT
    PRIMARY KEY IDENTITY,
    office_name    NVARCHAR(40) NOT NULL,
    office_address NVARCHAR(255) NOT NULL,
    phone          VARCHAR(20),
);

A continuación, inserta algunas filas en la tabla dbo.offices:

INSERT INTO
    dbo.offices(office_name, office_address)
VALUES
    ('Silicon Valley','400 North 1st Street, San Jose, CA 95130'),
    (Estrada Web Group,'1070 River Dr., Sacramento, CA 95820');

Luego, crea un procedimiento almacenado que encuentre las oficinas por ID de oficina:

CREATE PROC usp_get_office_by_id(
    @id INT
) AS
BEGIN
    SELECT
        *
    FROM
        dbo.offices
    WHERE
        office_id = @id;
END;

Después de eso, cambia esta tabla dbo.offices al esquema de sales:

ALTER SCHEMA sales TRANSFER OBJECT::dbo.offices;  

Si ejecutas el procedimiento almacenado usp_get_office_by_id, SQL Server generará un error:

Msg 208, Level 16, State 1, Procedure usp_get_office_by_id, Line 5 [Batch Start Line 30]
Invalid object name 'dbo.offices'.

Finalmente, modifica manualmente el procedimiento almacenado para reflejar el nuevo esquema:

ALTER PROC usp_get_office_by_id(
    @id INT
) AS
BEGIN
    SELECT
        *
    FROM
        sales.offices
    WHERE
        office_id = @id;
END;

Con esto ya puedes probar y ejecutar el procedimiento almacenado.

En este tutorial, aprendiste a usar la declaración ALTER SCHEMA de SQL Server para transferir un objeto de un esquema a otro dentro de la misma base de datos.