Esquemas en SQL Server
Los esquemas en SQL Server permiten organizar los elementos asegurables como tablas, vistas, procedimientos almacenados, etc y poder administrar mejor los permisos sobre estos.
Es posible crear esquemas para incluir las tablas de diferentes módulos en una empresa y poder separarlos al asignarles permisos a diferentes usuarios de base de datos. Para mayor información ver: Usuarios de base de datos, Inicios de sesión, Permisos con Grant.
Crear un esquema
La instrucción para crear un esquema tiene la siguiente sintaxis:
Create schema NombreEsquema [authorization Usuario]
Notas:
- Create schema debe ser la única instrucción del lote.
- El esquema por defecto donde se crean los objetos como tablas, vistas, procedimientos almacenados, etc es el esquema dbo (DataBase Owner).
- Si se utilizar instrucciones para crear tablas o vistas dentro del mismo bloque de la instrucción Create Schema, estos se crean dentro del esquema.
- Se pueden usar Grant y Deny para asignar o quitar los permisos sobre los asegurables en un esquema.
Ejercicios
Primero crear una base de datos. Se supone que existen las unidades C:, D. y E: para que funcione el ejercicio. (Ver Crear Base de datos)
xp_create_subdir ‘C:\Bases’
go
xp_create_subdir ‘D:\Bases’
go
xp_create_subdir ‘E:\Bases’
go
Create database Olimpiadas
on Primary
(Name=’Olimpiadas01′, Filename=’C:\Bases\Olimpiadas01.mdf’),
Filegroup VENTAS
(Name=’Olimpiadas02′, Filename=’D:\Bases\Olimpiadas02.ndf’)
log on
(Name=’Olimpiadas03′, Filename=’E:\Bases\Olimpiadas03.ldf’)
go
use Olimpiadas
go
1. — Crear esquemas: VENTAS, BANCOS, RRHH.
Create schema VENTAS
go
Create schema BANCOS
go
Create schema RRHH
go
2.– Listar los esquemas
select * from sys.schemas
go
Aparecen también los esquemas propios de una base de datos.
3. — Crear esquema PLANEAMIEMTO, evitar error si existe
if not exists (select * from sys.schemas where name = ‘PLANEAMIEMTO’)
Begin
Execute(‘Create schema PLANEAMIEMTO’)
End
go
Modificar un esquema
El modificar un esquema permite transferir asegurables de un esquema a otro.
Alter schema EsquemaDestino transfer EsquemaOrigen.Asegurable
El asegurable del esquema EsquemaOrigen se transfiere al EsquemaDestino
Los ejercicios respecto de este tema se incluyen en la creación de tablas, vistas y procedimientos almacenados.
Eliminar un esquema
Para eliminar un esquema se utiliza la instrucción
Drop schema NombreEsquema
Para eliminar el esquema debe estar vacío, es decir, sin asegurables.
Importante: Planificar los asegurables en esquemas debe ser al inicio de la creación de la BD, los cambios de esquemas de los asegurables no actualizan en los scripts en los que se hacen referencia a estos objetos.
Por ejemplo, si en un script se hace un listado de la tabla Empleados que se encuentra en el esquema RRHH debemos escribir:
select * from RRHH.Empleados
go
Si se cambia a otro esquema la tabla empleados y no se actualiza el script, este dejará de funcionar correctamente.
4. — Crear tabla Tiendas en el esquema Pruebas
Create schema Pruebas
Create table Tiendas
(
TiendasCodigo nchar(4),
TiendasDescripcion nvarchar(200) not null,
TiendasEstado nchar(1) constraint TiendasEstadoDF Default ‘A’,
constraint TiendasPK primary key (TiendasCodigo)
)
go
Note que después de la instrucción de crear el esquema no existe la instrucción Go y al crear la tabla Tiendas, aparentemente se crea en el esquema dbo, en este caso se creará en el esquema Pruebas.
Para crear otra tabla en el esquema Pruebas
Create table Pruebas.Otra
(
OtraCodigo nchar(4),
OtraDescripcion nvarchar(200) not null,
OtraEstado nchar(1) constraint OtraEstadoDF Default ‘A’,
constraint OtraPK primary key (OtraCodigo)
)
go
Note que la tabla Tiendas se ha creado en el mismo bloque de código del esquema, la tabla Otra para crearla en el esquema debemos escribir primero el nombre del esquema y luego el nombre de la tabla separadas por punto.