Busqueda en todas las tablas de la base de datos en SQL Server

En este ejemplo buscaremos un valor en todas las tablas de la base de datos, para esto consultaremos la metada de la base de datos con vistas de esquema de información

Una vista de INFORMATION_SCHEMA es uno de varios métodos de SQL Server que permite obtener metadatos, sin consultar directamente tablas del sistema, y además es un estándar ISO es decir que las vistas de INFORMATION_SCHEMA también se tienen en otros gestores de base de datos.

Usando estas vistas vamos consultar todas las tablas que tiene la base de datos, esto con la vista INFORMATION_SCHEMA.TABLES y a cada tabla le consultaremos los campos que posee con la vista INFORMATION_SCHEMA.COLUMNS, esto recalco para buscar un valor que no sabemos en que tabla de la base de datos se encuentra.

Declare @StrParametroBusqueda varchar(150)

Set @StrParametroBusqueda='Paris' -- <----Aquí agregamos el valor a buscar
CREATE TABLE #Resultado (NombreColumna nvarchar(500), ValorColumna nvarchar(3630))
SET NOCOUNT ON
DECLARE @NombreTabla varchar(300), @NombreColumna varchar(300), @StrValorABuscar varchar(300)
SET @NombreTabla = ''
SET @StrValorABuscar = QUOTENAME('%' + @StrParametroBusqueda + '%','''')
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 ' + @StrValorABuscar)
                                        END
                           END
       END
SELECT NombreColumna, ValorColumna FROM #Resultado
Drop table #Resultado