Moviendo y copiando tablas entre esquemas
En ocasiones es necesario mover o copiar tablas u otros objetos entre esquemas, algunos escenarios posibles son el rediseño de la base de datos donde se reorganizarán los objetos en diferentes esquemas, luego de hacer este proceso, es necesario cambiar los scripts como procedimientos almacenados, vistas, triggers, funciones definidas por el usuario, etc que hagan referencia a las tablas u objetos que se van a cambiar de esquema, si se realiza el cambio de esquema, tenga en cuenta todos los cambios posteriores que se deben hacer para que los sistemas funcionen correctamente.
En este artículo se muestran ejercicios para mover o copiar tablas entre esquemas, puede probar esto con vistas, procedimientos almacenados, triggers y cualquier otro objeto que se requiera pasar a otro esquema.
Para mas información ver:
Cursores en SQL Server
Variables en SQL Server
Estructura While en SQL Server
Esquemas en SQL Server
Ejercicio 1
Usando la base de datos AdventureWorks
use AdventureWorks
go
Las tablas en el esquema HumanResources se pueden ver en la siguiente imagen
Se va a crear un esquema RecursosHumanos y luego mover las tablas del esquema HumanResources a este nuevo esquema.
Create schema RecursosHumanos
go
Antes de mover las tablas, primero las vamos listar
select * from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ‘Base table’ and
TABLE_SCHEMA = ‘HumanResources’
go
La imagen muestra el resultado
Ahora, el cursor que va a mover las tablas del esquema HumanResources al esquema RecursosHumanos
DECLARE
@EsquemaActual nvarchar(20) = ‘HumanResources’,
@TablaMover nvarchar(50)
— Creamos el cursor
DECLARE cursorMoviendoTablas cursor FOR
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ‘Base table’ and
TABLE_SCHEMA = ‘HumanResources’
— Variable para generar la instrucción SQL
Declare @InstruccionSQL nvarchar(200)
OPEN cursorMoviendoTablas
Fetch cursorMoviendoTablas INTO @TablaMover
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @InstruccionSQL =
N’Alter schema RecursosHumanos transfer HumanResources’+ N’.[‘ + @TablaMover + N’]’
Execute (@InstruccionSQL);
Fetch cursorMoviendoTablas INTO @TablaMover
END
CLOSE cursorMoviendoTablas
DEALLOCATE cursorMoviendoTablas
go
Ver el listado de las tablas del esquema RecursosHumanos
select * from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ‘Base table’ and
TABLE_SCHEMA = ‘RecursosHumanos’
go
Puede ver en la imagen que ahora las tablas están en el nuevo esquema.
Importante
Debido a que en la base de datos AdventureWorks hay algunos script que hacen referencia a las tablas movidas al nuevo esquema, para no tener problemas, vamos a regresar las tablas de RecursosHumanos a HumanResources, usaremos el cursor creado cambiado los esquemas de origen y destino.
DECLARE
@EsquemaActual nvarchar(20) = ‘RecursosHumanos’,
@TablaMover nvarchar(50)
— Creamos el cursor
DECLARE cursorMoviendoTablas cursor FOR
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ‘Base table’ and
TABLE_SCHEMA = ‘RecursosHumanos’
— Variable para generar la instrucción SQL
Declare @InstruccionSQL nvarchar(200)
OPEN cursorMoviendoTablas
Fetch cursorMoviendoTablas INTO @TablaMover
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @InstruccionSQL =
N’Alter schema HumanResources transfer RecursosHumanos’+ N’.[‘ + @TablaMover + N’]’
Execute (@InstruccionSQL);
Fetch cursorMoviendoTablas INTO @TablaMover
END
CLOSE cursorMoviendoTablas
DEALLOCATE cursorMoviendoTablas
go
Ejercicio 2
Copiar las tablas del esquema Person a un nuevo esquema llamado Personal
Para copiar las tablas de un esquema a otro se va a usar la opción into NombreTabla de la instrucción Select (Ver Opciones del Select). Es necesario anotar que no se copiarán las restricciones de las tablas, esto quiere decir que las tablas en el esquema destino, para nuestro ejemplo, llamado Personal, no tendrán clave primaria, claves foráneas y ninguna otra restricción de las tablas originales.
Creamos el esquema Personal donde se copiarán las tablas.
Create schema Personal
go
Antes de copiar, listamos las tablas del esquema Person, estas tablas se van a copiar al nuevo esquema Personal
select * from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ‘Base table’ and
TABLE_SCHEMA = ‘Person’
go
La imagen muestra el resultado
Ahora el cursor para copiar todas las tablas del esquema Person al nuevo esquema Personal
DECLARE
@EsquemaActual nvarchar(10) = ‘Person’,
@TablaCopiar nvarchar(50)
DECLARE cursorCopiandoTablas cursor FOR
select TABLE_NAME from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ‘Base table’ and
TABLE_SCHEMA = ‘Person’
Declare @InstruccionSQL nvarchar(200)
OPEN cursorCopiandoTablas
Fetch cursorCopiandoTablas INTO @TablaCopiar
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @InstruccionSQL =
N’select * into Personal.’+ N'[‘+@TablaCopiar+N’] ‘+ ‘from Person’+ N’.[‘ + @TablaCopiar + N’]’
Execute (@InstruccionSQL)
Fetch cursorCopiandoTablas INTO @TablaCopiar
END
CLOSE cursorCopiandoTablas
DEALLOCATE cursorCopiandoTablas
go
Para visualizar las tablas del esquema Personal.
select * from INFORMATION_SCHEMA.TABLES
where TABLE_TYPE = ‘Base table’ and
TABLE_SCHEMA = ‘Personal’
go
La imagen muestra el resultado
Información de las restricciones del esquema Person (original)
Para tener información de las restricciones de las tablas del esquema Person, que es el origen desde donde se copiaron las tablas al nuevo esquema llamado Personal, podemos ejecutar las instrucciones siguientes:
Claves primarias y restricciones Unique
select
K.name As ‘Nombre PK’,
T.name As ‘Tabla’
from sys.key_constraints As K
join sys.tables As T on K.parent_object_id = T.object_id
where K.schema_id = (select schema_id from sys.schemas where name = ‘Person’)
go
La imagen muestra el resultado
Columnas que son identity
select
C.column_id, C.name As ‘Columna’, T.name As ‘Tabla’
from sys.identity_columns As C
join sys.tables As T on C.object_id = T.object_id
where C.object_id in (select object_id from sys.TABLES
where TYPE = ‘U’ and
schema_id = (select schema_id from sys.schemas where name = ‘Person’))
go
La imagen muestra el resultado
Claves Foráneas
select
F.name As ‘Nombre FK’,
(select T.name from sys.tables As T where T.object_id = F.parent_object_id) As ‘Tabla Principal’,
(select T.name from sys.tables As T where T.object_id = F.referenced_object_id) As ‘Tabla Referenciada’,
right(F.name, len(F.name) –
len(‘FK_’ + (select T.name from sys.tables As T where T.object_id = F.parent_object_id) + ‘‘ + (select T.name from sys.tables As T where T.object_id = F.referenced_object_id) + ‘‘ )
) As ‘Campo’
from sys.foreign_keys As F
join sys.tables As T on F.parent_object_id = T.object_id
where F.schema_id = (select schema_id from sys.schemas where name = ‘Person’)
go
La imagen muestra el resultado
Restricciones Check
select name, definition from sys.check_constraints
where schema_id = (select schema_id from sys.schemas where name = ‘Person’)
go
La imagen muestra el resultado
Restricciones de tipo Default
select name, definition from sys.default_constraints
where schema_id = (select schema_id from sys.schemas where name = ‘Person’)
go
La imagen muestra el resultado
