Siguiendo la línea de los últimos artículos acerca de la utilidad práctica de los metadatos del SQL Server, vamos a crear un script para buscar una cadena de texto en nuestra base de datos. Con unas pocas modificaciones, nos serviría también para buscar números u otro tipo de datos que admita el SQL Server.
Para buscar la cadena en nuestra base de datos, vamos a seguir los siguientes pasos:
- Recorrer las tablas de la base de datos. Para cada tabla:
- Crear consulta de búsqueda sql = "Select count(*) From " + NOM_TABLA + " Where 1=0"
- Recorrer los campos de la tabla actual. Para cada campo del tipo "char", "varchar" o "nvarchar":
- Agregar una condición de búsqueda a la cadena SQL (concatenada con OR):
- sql = sql + ' OR ' + NOM_COLUMNA + ' like ''%' + CADENA_BUSCAR + '%'''
- Continuar con el siguiente campo.
- Ejecutar la sentencia sql resultante de recorrer los campos de la tabla.
- Si el resultado de la ejecución (count(*)) es mayor que cero, se ha encontrado el valor buscado.
- Continuar con la siguiente tabla.
Se puede observar que en la SQL que vamos a crear, se añade como condición WHERE la sentencia 1=0. Esto se hace porque se puede dar el caso que una tabla no tenga campos de texto, en ese caso, la única condición sería 1=0 que no se cumple nunca, y que no nos devolvería ningún registro (count = 0).
En caso que la tabla contenga algún campo de tipo texto, se añadiría a la condición WHERE con el operador OR, y si se cumple que el campo contiene la cadena a buscar, el resultado de la condición WHERE sería true, con lo que el count(*) devolvería uno o más registros. Por ej.:
Select count(*) From Usuarios Where 1=0 OR Nombre like '%Pablo%'.
En este caso, el texto coloreado en verde es añadido por el script como un literal, mientras que el texto rojo se añade en función de los campos que tiene cada tabla. Si la condición marcada en rojo se cumple, toda la condición WHERE será verdadera.
Como siempre, debemos incluir al inicio del script, la instrucción Use, con tal de seleccionar la BBDD en la que queremos trabajar:
Use [miBaseDeDatos]
1. Recorrer las tablas de la Base de datos.
Para recorrer las tablas de nuestra base de datos, vamos a hacer uso de la información de Metadatos contenida en information_schema.tables. Podemos añadir una condición WHERE para filtrar las tablas en las que queremos buscar.
DECLARE CURSOR_SELECT
CURSOR FOR
SELECT TABLE_NAME
FROM information_schema.tables
OPEN CURSOR_SELECT FETCH NEXT FROM CURSOR_SELECT INTO @NOM_TABLA
-- Recorre las tablas de la base de datos
WHILE @@FETCH_STATUS = 0
BEGIN
/* Realiza las operaciones necesarias */
FETCH NEXT FROM CURSOR_SELECT INTO @NOM_TABLA
END
/* Libera la memoria */
CLOSE CURSOR_SELECT
DEALLOCATE CURSOR_SELECT
1.1. Crear consulta de búsqueda.
Por cada tabla, vamos a crear una consulta SQL que servirá para localizar la palabra que estamos buscando dentro de la tabla actual. Para ello constuiremos una cadena que se compone de una parte fija #Select count(*) From NOM_TABLA Where 1=0 # y a la que añadiremos los distintos campos de la base de datos.
print '-----------------------'
print 'TABLA: ' + @NOM_TABLA
print '-----------------------'
-- Crea la cadena de búsqueda
SET @PROXIMA_SENTENCIA = 'SELECT Count(*) FROM ' + @NOM_TABLA + ' WHERE 1=0'
Además aprovechamos para imprimir por consola el nombre de la tabla.
1.2. Recorrer los campos de la tabla actual.
Para recorrer los campos de una tabla, vamos a hacer uso de la información contenida en information_schema.columns. Por simplificar, vamos a limitar la búsqueda a campos de tipo texto (nvarchar, varchar y char).
En cada iteración, se añade una condición de búsqueda del tipo " OR Nombre_Columna like '%Cadena a buscar%' ".
-- Crea el cursor de campos de búsqueda
DECLARE CURSOR_CAMPOS
CURSOR FOR
SELECT COLUMN_NAME
FROM information_schema.columns
WHERE TABLE_NAME like @NOM_TABLA
AND (DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar' OR DATA_TYPE = 'char')
OPEN CURSOR_CAMPOS FETCH NEXT FROM CURSOR_CAMPOS INTO @NOM_COLUMNA
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROXIMA_SENTENCIA = @PROXIMA_SENTENCIA + ' OR ' + @NOM_COLUMNA + ' like ''%' + @CADENA_BUSCAR + '%'''
FETCH NEXT FROM CURSOR_CAMPOS INTO @NOM_COLUMNA
END
-- Libera memoria
CLOSE CURSOR_CAMPOS
DEALLOCATE CURSOR_CAMPOS
1.3. Ejecutar sentencia.
El siguiente paso, será ejecutar la sentencia que se ha construido para la tabla actual. Se imprimirá el resultado de registros obtenidos, si el resultado es mayor de cero, se habrá encontrado la palabra buscada.
-- Ejecuta la sentencia para comprobar si obtiene algún resultado
SELECT @sql = 'DECLARE CURSOR_SENTENCIA CURSOR FOR ' + @PROXIMA_SENTENCIA
EXEC sp_executesql @sql
OPEN CURSOR_SENTENCIA FETCH NEXT FROM CURSOR_SENTENCIA INTO @REGISTROS
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Registros ' + str(@REGISTROS)
FETCH NEXT FROM CURSOR_SENTENCIA INTO @REGISTROS
END
-- Libera memoria
CLOSE CURSOR_SENTENCIA
DEALLOCATE CURSOR_SENTENCIA
1.4. Continuar con la siguiente tabla.
Continuará con la siguiente tabla, repitiendo los pasos 1.1., 1.2. y 1.3.
Este ejemplo puede completarse con otros tipos de datos, seleccionando la manera adecuada para compararlos con el tipo de datos buscado.
Espero que os sea de utilidad!!
Código completo:
Use [miBaseDeDatos]
DECLARE @SQL NVARCHAR(4000)
DECLARE @PROXIMA_SENTENCIA NVARCHAR(3500)
DECLARE @NOM_TABLA NVARCHAR(255)
DECLARE @NOM_COLUMNA NVARCHAR(255)
DECLARE @REGISTROS int
DECLARE @CADENA_BUSCAR NVARCHAR(255)
SET @CADENA_BUSCAR = 'prueba'
DECLARE CURSOR_SELECT
CURSOR FOR
SELECT TABLE_NAME
FROM information_schema.tables
OPEN CURSOR_SELECT FETCH NEXT FROM CURSOR_SELECT INTO @NOM_TABLA
-- Recorre las tablas de la base de datos
WHILE @@FETCH_STATUS = 0
BEGIN
print '-----------------------'
print 'TABLA: ' + @NOM_TABLA
print '-----------------------'
-- Crea la cadena de búsqueda
SET @PROXIMA_SENTENCIA = 'SELECT Count(*) FROM ' + @NOM_TABLA + ' WHERE 1=0'
-- Crea el cursor de campos de búsqueda
DECLARE CURSOR_CAMPOS
CURSOR FOR
SELECT COLUMN_NAME
FROM information_schema.columns
WHERE TABLE_NAME like @NOM_TABLA
AND (DATA_TYPE = 'nvarchar' OR DATA_TYPE = 'varchar' OR DATA_TYPE = 'char')
OPEN CURSOR_CAMPOS FETCH NEXT FROM CURSOR_CAMPOS INTO @NOM_COLUMNA
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PROXIMA_SENTENCIA = @PROXIMA_SENTENCIA + ' OR ' + @NOM_COLUMNA + ' like ''%' + @CADENA_BUSCAR + '%'''
FETCH NEXT FROM CURSOR_CAMPOS INTO @NOM_COLUMNA
END
-- Libera memoria
CLOSE CURSOR_CAMPOS
DEALLOCATE CURSOR_CAMPOS
print @PROXIMA_SENTENCIA
-- Ejecuta la sentencia para comprobar si obtiene algún resultado
SELECT @sql = 'DECLARE CURSOR_SENTENCIA CURSOR FOR ' + @PROXIMA_SENTENCIA
EXEC sp_executesql @sql
OPEN CURSOR_SENTENCIA FETCH NEXT FROM CURSOR_SENTENCIA INTO @REGISTROS
WHILE @@FETCH_STATUS = 0
BEGIN
print 'Registros ' + str(@REGISTROS)
FETCH NEXT FROM CURSOR_SENTENCIA INTO @REGISTROS
END
-- Libera memoria
CLOSE CURSOR_SENTENCIA
DEALLOCATE CURSOR_SENTENCIA
FETCH NEXT FROM CURSOR_SELECT INTO @NOM_TABLA
END
/* Libera la memoria */
CLOSE CURSOR_SELECT
DEALLOCATE CURSOR_SELECT
