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

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