Search for a value in selected columns

We know a name, email or reference, but we don’t remember which of several columns of text it was saved in. Generating a search over the entire database would be expensive and could read information we don’t need.

What we need to distinguish

An allowlist limits in advance which columns may be queried. QUOTENAME delimits an identifier safely; a parameter sends the value separately from dynamic SQL, which is code built during execution.

We will first define an explicit list of allowed columns. Identifiers will be delimited with QUOTENAME and the value will be sent as a parameter; This way we avoid concatenating the data sought within the dynamic SQL.

Query

/*
    Antes de ejecutar:
        Revisa la base activa, los nombres de objetos, los parámetros y los permisos.
        Adapta el ejemplo a tu modelo y pruébalo primero en un ambiente controlado.
*/

SET NOCOUNT ON;
GO
-- Consulta
DECLARE @Valor NVARCHAR(200) = N'Pedido 1';
DECLARE @CoincidenciaExacta BIT = 0;
DECLARE @Patron NVARCHAR(450);
DECLARE @Sql NVARCHAR(MAX);

DECLARE @Columnas TABLE
(
    Esquema SYSNAME,
    Tabla SYSNAME,
    Columna SYSNAME,
    PRIMARY KEY (Esquema, Tabla, Columna)
);

INSERT INTO @Columnas (Esquema, Tabla, Columna)
VALUES
    (N'dbo', N'Cliente', N'Nombre'),
    (N'dbo', N'Cliente', N'Correo'),
    (N'dbo', N'MovimientoInventario', N'Referencia');

IF EXISTS
(
    SELECT 1
    FROM @Columnas AS x
    WHERE NOT EXISTS
    (
        SELECT 1
        FROM sys.tables AS t
        INNER JOIN sys.schemas AS s
            ON s.schema_id = t.schema_id
        INNER JOIN sys.columns AS c
            ON c.object_id = t.object_id
        WHERE s.name = x.Esquema
          AND t.name = x.Tabla
          AND c.name = x.Columna
          AND c.system_type_id IN
              (TYPE_ID(N'char'), TYPE_ID(N'varchar'),
               TYPE_ID(N'nchar'), TYPE_ID(N'nvarchar'))
    )
)
    THROW 50020, N'La lista contiene una columna inexistente o no textual.', 1;

SET @Patron = CASE
    WHEN @CoincidenciaExacta = 1 THEN @Valor
    ELSE N'%' +
         REPLACE(REPLACE(REPLACE(REPLACE
         (@Valor, N'\', N'\\'), N'%', N'\%'), N'_', N'\_'), N'[', N'\[') +
         N'%'
END;

SELECT @Sql = STRING_AGG
(
    CAST
    (
        N'SELECT N' + QUOTENAME(x.Esquema, '''') + N' AS Esquema, ' +
        N'N' + QUOTENAME(x.Tabla, '''') + N' AS Tabla, ' +
        N'N' + QUOTENAME(x.Columna, '''') + N' AS Columna, ' +
        N'CONVERT(nvarchar(4000), ' + QUOTENAME(x.Columna) +
        N') AS ValorEncontrado FROM ' + QUOTENAME(x.Esquema) + N'.' +
        QUOTENAME(x.Tabla) + N' WHERE CONVERT(nvarchar(4000), ' +
        QUOTENAME(x.Columna) + N') ' +
        CASE WHEN @CoincidenciaExacta = 1
             THEN N'= @Patron'
             ELSE N'LIKE @Patron ESCAPE N''\'''
        END
        AS NVARCHAR(MAX)
    ),
    N' UNION ALL '
)
FROM @Columnas AS x;

EXEC sys.sp_executesql
    @Sql,
    N'@Patron nvarchar(450)',
    @Patron = @Patron;
GO

Parameters or permissions

Change @Value and @Columns rows. The search is limited to text types and the first 4,000 converted characters; For max columns with long documents it is advisable to design another strategy. QUOTENAME protects schema, table, and column names; It does not replace the parameterization of values. sp_executesql receives @Patron separately. Read permissions on all selected columns and visibility of their metadata are required.

How to read the result

Each match indicates schema, table, column, and value found. In partial mode we escape , %, _ and [ to treat them as text characters and not as LIKE wildcards. STRING_AGG builds a query per column and joins them with UNION ALL. Even though the identifiers are delimited, each selected column may require a scan if there is no proper search strategy. The comparison uses the collation of each column. Therefore sensitivity to capital letters, accents and other characters may vary between tables.

What this does establish

Allows you to locate a value in an explicit, searchable set of textual columns without inserting the value directly into dynamic SQL.

What this still does not establish

It does not automatically search the entire database, cover binaries, or compare numbers or dates with their original type. It also does not guarantee good performance on large tables or partial searches with initial wildcard.

Next reasonable check

If a column concentrates recurring searches, review its access model and avoid turning this research query into a regular operation of the application.

Search SQL Server