Esquemas – Crear – Modificar – Eliminar

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. (Ver Permisos con Grant)

En una base de datos se pueden incluir los elementos de varios módulos que se desarrollen en una organización, por ejemplo, el módulo de Ventas, el de Planilla, el módulo de Contabilidad, etc y es obvio que la mayor parte de asegurables de cada uno de los módulos no son compartidos, es decir, el que tiene permisos para ver los asegurables del esquema de Ventas no tiene acceso a los de Contabilidad, eso es posible usando permisos y los asegurables en Esquemas.

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. (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

Instrucción Alter Schema

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

Instrucción Drop Schema

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

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.