¿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 serSYS
niINFORMATION_SCHEMA
.- El
entity_type
puede ser objeto, tipo o colección de esquemas XML. Por defecto es Objeto. Elentity_type
representa la clase de la entidad para la que se cambia el propietario. object_name
es el nombre del objeto que deseas mover atarget_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.