viernes, 4 de diciembre de 2020

Ejecutar la misma consulta a todas las bases de Datos SQL SERVER

 En ocasiones necesitamos realizar el mismo query en todas las bases de datos de un servidor. Imaginemos un servidor que tiene muchas bases de datos, cada base de datos es una sucursal, y nos gustaría saber, en que base de datos hay empleados dados de alta y en cual no, esto para saber que base de datos sobra y poderla borrar, o para lo que se les ocurra. 


Pues bien, podemos pensar en utilizar un select a la tabla de empleados a cada una de las sucursales, esto es correcto, pero sería demasiado tardado, imaginemos 100 bases de datos, entonces, partiendo de que las bases de datos son iguales, aplicaríamos la misma consulta a las 100 bases mediante el procedimiento almacenado de sistema sp_MSforeachdb, y lo vamos a revisar a continuación.

El procedimiento sp_MSforeachdb no esta documentado en Microsoft por lo que resulta complejo entenderlo, sin embargo, este procedimiento utiliza un parámetro de tipo varchar para enviar el comando que le indiquemos. Pero vamos a realizarlo que es como aprendemos y como nos gusta.


Creando las bases de datos


Vamos a crear las bases de datos de ejemplos, en este caso serán tres, y serán sucursales.

create database SUCURSAL1

GO

 

CREATE DATABASE SUCURSAL2

GO

 

CREATE DATABASE SUCURSAL3

GO



Creando las tablas de empleados en las tres sucursales


Una vez creadas las bases de datos para el ejemplo, vamos a crear las tablas de Empleados, la misma para las tres sucursales

use SUCURSAL1

GO

 

CREATE TABLE EMPLEADOS(

ID INT PRIMARY KEY IDENTITY,

NOMBRE VARCHAR(MAX),

APELLIDOP VARCHAR(MAX),

APELLIDOM VARCHAR(MAX))

GO

 

use SUCURSAL2

GO

 

CREATE TABLE EMPLEADOS(

ID INT PRIMARY KEY IDENTITY,

NOMBRE VARCHAR(MAX),

APELLIDOP VARCHAR(MAX),

APELLIDOM VARCHAR(MAX))

GO

 

use SUCURSAL3

GO

 

CREATE TABLE EMPLEADOS(

ID INT PRIMARY KEY IDENTITY,

NOMBRE VARCHAR(MAX),

APELLIDOP VARCHAR(MAX),

APELLIDOM VARCHAR(MAX))

GO


Llenando las tablas de las sucursales


use SUCURSAL1

GO

 

INSERT INTO EMPLEADOS (NOMBRE, APELLIDOP, APELLIDOM) VALUES ('EMMANUEL', 'URIAS', 'ROBLES')

INSERT INTO EMPLEADOS (NOMBRE, APELLIDOP, APELLIDOM) VALUES ('JESUS', 'GUTIERREZ', 'LANDA')

INSERT INTO EMPLEADOS (NOMBRE, APELLIDOP, APELLIDOM) VALUES ('JAVIER', 'HERNANDEZ', 'BALCAZAR')

 

USE SUCURSAL3

GO

 

INSERT INTO EMPLEADOS (NOMBRE, APELLIDOP, APELLIDOM) VALUES ('MARCELA', 'GUERRERO', 'ESPINOZA')

INSERT INTO EMPLEADOS (NOMBRE, APELLIDOP, APELLIDOM) VALUES ('LAURA', 'SERRANO', 'FLORES')

INSERT INTO EMPLEADOS (NOMBRE, APELLIDOP, APELLIDOM) VALUES ('LUISA', 'ROBLES', 'MENDOZA')

INSERT INTO EMPLEADOS (NOMBRE, APELLIDOP, APELLIDOM) VALUES ('PATRICIA', 'FERNANDEZ', 'GONZALEZ')


Como vemos, en la sucursal 1 tenemos 3 empleados, en la sucursal 4, tenemos 4 empleados y en la 3 no tenemos ninguno.

Ejecutar la misma consulta en todas las bases de datos


Vamos ahora a obtener el numero de empleados que existen en cada sucursal, en este caso podríamos hacerlo base por base, porque solo son 3, pero si fueran 100, sería mucho tiempo perdido, así que vamos a obtener el numero de empleados por sucursal, con una sola consulta que recorra todas las bases de datos de nuestro servidor. Realizaremos la siguiente consulta en SUCURSAL1

use SUCURSAL1

GO

SELECT COUNT(*) as [NUMERO DE EMPLEADOS] FROM EMPLEADOS

GO.



Como vemos, al ejecutarla en la SUCURSAL 1, obtenemos que hay 3 empleados, al ejecutarla en la SUCURSAL2, obtendremos un 0 y en la sucursal 3 un 4. Pero entremos en materia utilizando el sp_MSforeachdb 


use master

go

 

DECLARE @COMANDO varchar(max)

set @COMANDO = 'use [?] SELECT COUNT(*) as [NUMERO DE EMPLEADOS] FROM EMPLEADOS'

exec sp_msForEachDb @comando


Al ejecutar este query, nos percatamos que aparece el siguiente mensaje de error: 

Msg 208, Level 16, State 1, Line 3

Invalid object name 'EMPLEADOS'.

Msg 208, Level 16, State 1, Line 3

Invalid object name 'EMPLEADOS'.

Msg 208, Level 16, State 1, Line 3

Invalid object name 'EMPLEADOS'.

Msg 208, Level 16, State 1, Line 3

Invalid object name 'EMPLEADOS'.


Esto es debido a que al recorrer todas las bases de datos del servidor tambien recorre las bases msdb, model, master y temp, donde no existen esas tablas y por eso el mensaje de error, lo que debemos hacer es excluir de la consulta esas bases de datos, y para eso utilizaremos la sentencia IF combinada con el NOT IN

use master

go

 

DECLARE @COMANDO varchar(max)

set @COMANDO = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')

BEGIN

USE ?

SELECT COUNT(*) as [NUMERO DE EMPLEADOS] FROM EMPLEADOS

END'

exec sp_msForEachDb @comando


Explicando el query anterior, vemos que realizará la consulta mientras las bases de datos no se llamen como las del sistema, el operador ?, representa a la base de datos actual del ciclo. Al ejecutar el query anterior obtendremos algo como esto:


Ahora tenemos un nuevo detalle, la consulta, como vemos, ya se ejecutó correctamente, pero ¿como sabemos a qué base de datos se refiere?, bueno, para eso modificaremos un poco el select anterior, y le agregaremos DB_NAME()

use master

go

 

DECLARE @COMANDO varchar(max)

set @COMANDO = 'IF ''?'' NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')

BEGIN

USE ?

SELECT DB_NAME(), COUNT(*) as [NUMERO DE EMPLEADOS] FROM EMPLEADOS

END'

exec sp_msForEachDb @comando

Ahora si, al ejecutar la sentencia anterior, obtendremos este resultado:



Como vemos, ahora si aparece el nombre de la sucursal, con la cantidad de empleado que tiene cada una, ya sería cuestión de jugar con operador WHERE para mostrar las que no tienen empleados, o las que si tienen empleados, pero eso ya será de acuerdo a la necesidad.


Hasta aquí el post, espero compartan, comenten, pregunten o den clic a algún anuncio patrocinado, eso me ayudaría bastante.


Saludos




 

No hay comentarios.:

Publicar un comentario

Catalogo de Clientes - Contpaqi Comercial

  Buenas a todos, en esta ocasión seguimos con el curso de Contpaqi ©  Comercial y veremos la opciones del catálogo de  Clientes , parte med...