SQL SERVER (I) Buscar que tabla y campo contiene un valor. Buscar nombres de campo

1. Buscar valores en una BD. VErsion Google

Me parece muy interesantes las repuestas de stackoverflow 

Una de las que más me ha gustado es de sbrbot  que he adaptado un poco para el caso de tipo "string"

DECLARE @search VARCHAR(100), @table SYSNAME, @column SYSNAME, @schema SYSNAME


DECLARE curTabCol CURSOR FOR
    SELECT c.TABLE_SCHEMA , c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.TABLES t 
      ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_TYPE='BASE TABLE' -- avoid views
    WHERE c.DATA_TYPE IN ('varchar','nvarchar','text','ntext','char','nchar') -- searching only in these column types
    --AND c.COLUMN_NAME IN ('NAME','DESCRIPTION') -- searching only in these column names
    ORDER BY c.TABLE_NAME, c.COLUMN_NAME 

SET @search='PHXN'

OPEN curTabCol
FETCH NEXT FROM curTabCol INTO @schema, @table, @column

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXECUTE('IF EXISTS 
             (SELECT * FROM ' + @schema + '.[' + @table + '] WHERE [' + @column + '] LIKE ''%' + @search + '%'') 
             PRINT ''' + @table + '.' + @column + '''')
    FETCH NEXT FROM curTabCol INTO  @schema, @table, @column
END

CLOSE curTabCol
DEALLOCATE curTabCol

y para tipo "enteros"

DECLARE @search int, @table SYSNAME, @column SYSNAME, @schema SYSNAME

DECLARE curTabCol CURSOR FOR
    SELECT c.TABLE_SCHEMA , c.TABLE_NAME, c.COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS c
    JOIN INFORMATION_SCHEMA.TABLES t 
      ON t.TABLE_NAME=c.TABLE_NAME AND t.TABLE_TYPE='BASE TABLE' -- avoid views
    WHERE c.DATA_TYPE IN ('numeric','int','bigint','decimal') -- searching only in these column types
    --AND c.COLUMN_NAME IN ('NAME','DESCRIPTION') -- searching only in these column names
    ORDER BY c.TABLE_NAME, c.COLUMN_NAME 


--SET @search='SPHXN-YNS5H-9TV6R'
--SET @search='10BC9C2A9F54521E4AE70402CCFC4A87F569F8A8'
SET @search=418534

OPEN curTabCol
FETCH NEXT FROM curTabCol INTO @schema, @table, @column

WHILE (@@FETCH_STATUS = 0)
BEGIN
    --PRINT '                                  ' + @table + '.' + @column
    EXECUTE('IF EXISTS 
             (SELECT * FROM ' + @schema + '.[' + @table + '] WHERE [' + @column + '] = ' + @search + ') 
              PRINT ''' + @table + '.' + @column + '''')
    FETCH NEXT FROM curTabCol INTO @schema, @table, @column
END

CLOSE curTabCol
DEALLOCATE curTabCol

2. Buscar valores en una BD. Version Claude

DECLARE @db SYSNAME = N'BIB_AY';--'ACCEDE_ADM';---BIB_AY';--'SCWAY2026';
DECLARE @valor NVARCHAR(200) = '14-049'; --'220260004314'; --'220260007686' -- '320260001544';
DECLARE @schema NVARCHAR(128), @tabla NVARCHAR(128);
DECLARE @sql NVARCHAR(MAX), @sqlCount NVARCHAR(MAX);
DECLARE @cnt INT;

-- 1. Tablas de la base de datos indicada (sin depender de USE)
IF OBJECT_ID('tempdb..#Tablas') IS NOT NULL DROP TABLE #Tablas;
CREATE TABLE #Tablas (Esquema NVARCHAR(128), Tabla NVARCHAR(128));

SET @sql = N'
SELECT s.name, t.name
FROM ' + QUOTENAME(@db) + N'.sys.tables t
JOIN ' + QUOTENAME(@db) + N'.sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0';
INSERT INTO #Tablas (Esquema, Tabla) EXEC sp_executesql @sql;

-- 2. Columnas candidatas (texto/numéricas) de esa misma base de datos
IF OBJECT_ID('tempdb..#Columnas') IS NOT NULL DROP TABLE #Columnas;
CREATE TABLE #Columnas (Esquema NVARCHAR(128), Tabla NVARCHAR(128), Columna NVARCHAR(128));

SET @sql = N'
SELECT s.name, t.name, c.name
FROM ' + QUOTENAME(@db) + N'.sys.columns c
JOIN ' + QUOTENAME(@db) + N'.sys.tables t ON c.object_id = t.object_id
JOIN ' + QUOTENAME(@db) + N'.sys.schemas s ON t.schema_id = s.schema_id
JOIN ' + QUOTENAME(@db) + N'.sys.types ty ON c.user_type_id = ty.user_type_id
WHERE t.is_ms_shipped = 0
  AND ty.name IN (''varchar'',''nvarchar'',''char'',''nchar'',''text'',''ntext'',
                  ''int'',''bigint'',''smallint'',''tinyint'',''decimal'',''numeric'')';
INSERT INTO #Columnas (Esquema, Tabla, Columna) EXEC sp_executesql @sql;

-- 3. Recorrer tabla por tabla
DECLARE cur CURSOR FOR SELECT Esquema, Tabla FROM #Tablas ORDER BY Esquema, Tabla;
OPEN cur;
FETCH NEXT FROM cur INTO @schema, @tabla;

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Consultando tabla: ' + @db + '.' + @schema + '.' + @tabla;

    SELECT @sql = STRING_AGG(CAST(
        'SELECT ''' + @db + '.' + @schema + '.' + @tabla + ''' AS Tabla, ''' + Columna + ''' AS Campo, TRY_CAST([' + Columna + '] AS NVARCHAR(4000)) AS Valor
         FROM ' + QUOTENAME(@db) + '.' + QUOTENAME(@schema) + '.' + QUOTENAME(@tabla) + '
         WHERE TRY_CAST([' + Columna + '] AS NVARCHAR(4000)) LIKE ''%' + @valor + '%'''
    AS NVARCHAR(MAX)), ' UNION ALL ')
    FROM #Columnas
    WHERE Esquema = @schema AND Tabla = @tabla;

    SET @cnt = 0;

    IF @sql IS NOT NULL
    BEGIN
        SET @sqlCount = N'SELECT @cntOut = COUNT(*) FROM (' + @sql + N') AS x';
        EXEC sp_executesql @sqlCount, N'@cntOut INT OUTPUT', @cntOut = @cnt OUTPUT;

        PRINT '  -> ' + CAST(@cnt AS NVARCHAR(20)) + ' registro(s) encontrado(s)';

        IF @cnt > 0
            EXEC sp_executesql @sql;
    END
    ELSE
    BEGIN
        PRINT '  -> Sin columnas candidatas (texto/numéricas) en esta tabla';
    END

    SET @sql = NULL;
    FETCH NEXT FROM cur INTO @schema, @tabla;
END

CLOSE cur;
DEALLOCATE cur;
DROP TABLE #Tablas;
DROP TABLE #Columnas;

3. Buscar nombres de campos que contengan un substring. Version claude

DECLARE @nombre_bd sysname = 'BIB_AY';
DECLARE @esquema   sysname = 'dbo';
DECLARE @buscar    nvarchar(100) = '%EXP%';   -- <-- cambia aqui el substring a buscar

DECLARE @sql nvarchar(max) = N'
SELECT
    s.name  AS esquema,
    t.name  AS tabla,
    c.name  AS columna,
    ty.name AS tipo_dato,
    c.max_length
FROM ' + QUOTENAME(@nombre_bd) + N'.sys.columns c
JOIN ' + QUOTENAME(@nombre_bd) + N'.sys.tables  t  ON t.object_id = c.object_id
JOIN ' + QUOTENAME(@nombre_bd) + N'.sys.schemas s  ON s.schema_id = t.schema_id
JOIN ' + QUOTENAME(@nombre_bd) + N'.sys.types   ty ON ty.user_type_id = c.user_type_id
WHERE s.name = @esquema
  AND c.name LIKE @buscar
ORDER BY t.name, c.name;';

EXEC sp_executesql @sql, N'@esquema sysname, @buscar nvarchar(100)', @esquema, @buscar;