sábado, 19 de julio de 2014

SQL Server. Buscar un valor en toda la base de datos

Algunas veces necesitamos buscar un valor determinado en toda una base de datos con la dificultad que esto conlleva, sobre todo si tiene muchas tablas y muchos campos. 

Para realizar esa búsqueda podemos crear este procedimiento almacenado que hará una búsqueda bastante tediosa por nosotros.

El procedimiento simplemente genera una tabla auxiliar que almacena el resultado de la búsqueda que va realizando tabla por tabla y campo por campo. Es bastante sencillo. Se copia este código y se pega en el analizador de consultas del SQL Server, se ejecuta este código para que genere un procedimiento almacenado llamado dbo.BuscaValorEnBBDD




CREATE PROC BuscaValorEnBBDD
(
@StrValorBusqueda nvarchar(100)

)
AS
BEGIN
CREATE TABLE #Resultado (NombreColumna nvarchar(370), ValorColumna nvarchar(3630))

SET NOCOUNT ON DECLARE @NombreTabla nvarchar(256),

@NombreColumna nvarchar(128),
@StrValorBusqueda2 nvarchar(110)

SET @NombreTabla = ''
SET @StrValorBusqueda2 = QUOTENAME('%' + @StrValorBusqueda + '%','''')
WHILE @NombreTabla IS NOT NULL

BEGIN
SET @NombreColumna = ''
SET @NombreTabla =

(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @NombreTabla
AND OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0)
WHILE (@NombreTabla IS NOT NULL) AND (@NombreColumna IS NOT NULL)
BEGIN
SET @NombreColumna =
(SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@NombreTabla, 2)
AND TABLE_NAME = PARSENAME(@NombreTabla, 1)
AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
AND QUOTENAME(COLUMN_NAME) > @NombreColumna)
IF @NombreColumna IS NOT NULL
BEGIN
INSERT INTO #Resultado
EXEC
('SELECT ''' + @NombreTabla + '.' + @NombreColumna + ''', LEFT(' + @NombreColumna + ', 3630)
FROM ' + @NombreTabla + ' (NOLOCK) ' + ' WHERE ' + @NombreColumna + ' LIKE ' + @StrValorBusqueda2)
END
END
END
SELECT NombreColumna, ValorColumna FROM #Resultado
END



Una vez ejecutado este código en el analizador de consultas podemos comprobar que se ha generado un procedimiento almacenado llamado dbo.BuscaValorEnBBDD



SQL Server. Buscar un valor en toda la base de datos


Si queremos que el texto a buscar sea exacto podemos optar a sustituir la línea del script 

SET @StrValorBusqueda2 = QUOTENAME('%' + @StrValorBusqueda + '%','''')

por

SET @StrValorBusqueda2 = QUOTENAME( @StrValorBusqueda ,'''')

De este modo sólo mostrará los lugares donde coincida ese texto exactamente.

Para buscar algo basta con escribir lo siguiente en el analizador de consultas y ejecutar:
EXEC dbo.BuscaValorEnBBDD 'Texto a buscar'

En caso de encontarlo nos dirá en que propietario, en que tabla y en que columna se encuentra. En este caso hemos buscado el texto 'HA ENTRADO' y nos devuelve este resultado.


Buscar un valor en toda la base de datos


Buscar un objeto de Base de Datos. (Tabla, Vista, Procedimiento...)



Si en vez de un valor lo que quiero es buscar un objeto determinado, ya sea una tabla, una vista, un procedimiento almacenado , etc. Para ello basta con hacer la siguiente SELECT:

SELECT * FROM sysobjects WHERE name like '%nombre_objeto%'


El resultado será un poco pobre en el sentido de que no dice gran cosa aparte del tipo de objeto que es (en el campo xtype mostrara una V si es una Vista, T si es tabla, etc.) y su ID de objeto. Pero puede servirnos al menos para asegurarnos si el objeto buscado existe o no en la Base de Datos y de que tipo es.


Buscar un valor entero

Después de publicar este post tuve un par de lectores preguntándome como hacer para buscar valores enteros, la verdad es que tuve que investigarlo pero las modificaciones no han sido muy laboriosas, basta sustituir la definición de la variable @ValorBusqueda de nvarchar a integer, luego donde pone DATA_TYPE IN se quitan las definiciones de ('char', 'varchar', 'nchar', 'nvarchar') y se sustituyen por ('integer','tinyint','bit') y finalmente se sustituye ' LIKE ' por ' = ' para que nadie pierda el tiempo haciendo cambios que copie este código.

CREATE PROC BuscaValorEnBBDD
(
@StrValorBusqueda integer

)
AS
BEGIN
CREATE TABLE #Resultado (NombreColumna nvarchar(370), ValorColumna integer)

SET NOCOUNT ON DECLARE @NombreTabla nvarchar(256), 

@NombreColumna nvarchar(128)

SET @NombreTabla = ''
WHILE @NombreTabla IS NOT NULL

BEGIN
SET @NombreColumna = ''
SET @NombreTabla = 

(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @NombreTabla
AND OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0)
WHILE (@NombreTabla IS NOT NULL) AND (@NombreColumna IS NOT NULL)
BEGIN
SET @NombreColumna =
(SELECT MIN(QUOTENAME(COLUMN_NAME))
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = PARSENAME(@NombreTabla, 2)
AND TABLE_NAME = PARSENAME(@NombreTabla, 1)
AND DATA_TYPE IN ('integer','tinyint','bit')
AND QUOTENAME(COLUMN_NAME) > @NombreColumna)
IF @NombreColumna IS NOT NULL
BEGIN
INSERT INTO #Resultado
EXEC
('SELECT ''' + @NombreTabla + '.' + @NombreColumna + ''', LEFT(' + @NombreColumna + ', 3630) 
FROM ' + @NombreTabla + ' (NOLOCK) ' + ' WHERE ' + @NombreColumna + ' = ' + @StrValorBusqueda)
END 
END 
END
SELECT NombreColumna, ValorColumna FROM #Resultado

END

La busqueda deberá hacerse sin poner comillas en el valor buscado.

EXEC dbo.BuscaValorEnBBDD 4

Si buscamos un valor decimal o float truncará la búsqueda al valor entero más cercano, todavía no he conseguido modificar este procedimiento para que encuentre un número decimal exacto.

19 comentarios:

  1. Gracias sirve perfecto. pregunto ¿el consumo es muy alto?

    ResponderEliminar
  2. Dependerá del tamaño de la base de datos, el SP no ocupa mucho y tampoco parece que consuma muchos recursos.

    ResponderEliminar
  3. como puedo modificarlo para poder obtener el resultado en una variable y poder guardar ese resultado

    ResponderEliminar
  4. Pues igual que se ha definido una variable de entrada @strValorBusqueda puedes definir otras del estilo @strValorResultado1, @strValorResultado2, etc y al final les asignas la salida de la SELECT, ten en cuenta que este caso sólo podrás obtener un resultado, en caso de que haya múltiples registros deberás montar un cursor para rellenar un array o una tabla temporal.

    ResponderEliminar
  5. Buen aporte, totalmente agradecido.

    ResponderEliminar
  6. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  7. Hola, funciona de maravilla, pero solo vale para cadena de texto?? Porque cuando intento buscar un valor numérico no lo encuentra.... Bueno sí que encuentra pero solo me da resultados de casillas donde hay números y texto a la vez, pero no me da en los resultados de la busqueda casillas (que sé que hay) donde el valor es un "número puro", sin ningún texto. Que tendría que hacer para que también me encontrase dichos valores númericos ???

    ResponderEliminar
    Respuestas
    1. Pues he estado haciendo pruebas y no he sido capaz que modificar el procedimiento para que busque enteros, la verdad es que sería ideal. Si alguien sabe como hacerlo agradecería que nos indicase cómo.

      Gracias y saludos.

      Eliminar
    2. ¡Lo he conseguido! mira la parte final del post. He conseguido modificar el procedimiento para que busque enteros, pero aun no he conseguido que busque decimales, los trunca a enteros.

      Eliminar
  8. Hola como estas ya utilice tu algoritmo y esta realmente genial gracias, solo una cosa como se modificaría para que me muestre en el resultado de la búsqueda toda la información de esa fila osea no solo en campo si no también toda la fila de ese campo en los resultados.

    ResponderEliminar
    Respuestas
    1. Para hacer eso tendrías que hacer una SELECT dinámica bastante compleja que tome el valor de cada columna. Date cuenta que a priori no sabemos el número de columnas de la tabla pero la tabla de salida siempre tiene dos columnas y no n.
      Se me antoja bastante complejo aunque seguramente alguien sepa hacerlo. No es mi caso.
      De todas formas para salir del paso una vez obtenido el resultado puedes hacer un simple SELECT * FROM tbNombreTabla WHERE Nombre_Columna = 'valor a Buscar'
      pues ya conoces el nombre de la tabla y de la columna que te ha devuelto el procedimiento.

      Eliminar
  9. Excelente aporte, funcional al 100%. Gracias.

    ResponderEliminar
  10. y si es un numero? como se busca por un numero

    ResponderEliminar
    Respuestas
    1. ¡Lo he conseguido! mira la parte final del post. Gracias a tu pregunta y a la de VeNtU he conseguido modificar el procedimiento para que busque enteros.

      Eliminar