Esquemas SQL Server

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.