Buscar un dato en TODAS las tablas de una Base de Datos

Question

Tengo una base de datos con muchas tablas que debo analizar. Supongamos que deseo saber donde o en que tabla se encuentra cierta información, sobre la base de que seria tedioso buscar tabla por tabla y campo por campo manualmente. 

¿Es posible en SQL hacer una búsqueda secuencial por tablas dentro de una base de datos, que busque en el contenido de las mismas una valor o cadena especifico (contenido en alguna parte) identificando la tabla que lo contiene?

Es como si dijéramos a SQL, búscame en cada tabla, cada campo de cada tabla, este valor y dime en que tabla esta...

Gracias de antemano

---

Luis C

Answer 1

Hola Luis Carlos H:

Si es posible, y a priori no muy complejo. Te expongo un ejemplo donde realizó esto mismo, para que puedas ver un poco como se hace y como se puede acomodar a tú base de datos.

CREATE DATABASE [EJEMPLO2];
GO
USE [EJEMPLO2];
GO
CREATE TABLE TABLA1
(ID   INT IDENTITY(1, 1),
 COL1 VARCHAR(1000),
 COL2 INT,
 COL3 NVARCHAR(400),
 COL4 DATE
);
GO
CREATE TABLE TABLA2
(ID   INT IDENTITY(1, 1),
 COL1 VARCHAR(1000),
 COL2 INT,
 COL3 NVARCHAR(400),
 COL4 DATE
);
GO
CREATE TABLE TABLA3
(ID   INT IDENTITY(1, 1),
 COL1 VARCHAR(1000),
 COL2 INT,
 COL3 NVARCHAR(400),
 COL4 DATE
);
GO
INSERT INTO TABLA1
(COL1,
 COL2,
 COL3,
 COL4
)
VALUES
('EJEMPLO',
 123456,
 N'EJEMPLO',
 '20180801'
),
('EJEMPLO',
 0,
 N'EJEMPLO',
 '20180801'
),
('EJ123456EE',
 0,
 N'EJEMPLO',
 '20180801'
);
GO
INSERT INTO TABLA2
(COL1,
 COL2,
 COL3,
 COL4
)
VALUES
('EJEMPLO',
 0,
 N'EJEMPLO',
 '20180801'
),
('EJEMPLO',
 0,
 N'EJEMPLO',
 '20180801'
),
('EJEE',
 0,
 N'EJEMPLO',
 '20180801'
);
GO
INSERT INTO TABLA3
(COL1,
 COL2,
 COL3,
 COL4
)
VALUES
('EJEMPLO',
 0,
 N'EJEMPLO',
 '20180801'
),
('EJEMPLO',
 0,
 N'EJE123456MPLO',
 '20180801'
),
('EJ123456EE',
 0,
 N'EJEMPLO',
 '20180801'
);
GO
SELECT *
FROM TABLA1;
SELECT *
FROM TABLA2;
SELECT *
FROM TABLA3;
GO

Tenemos varias tablas, varios tipos de datos diferentes.

Solución a modificar:

DECLARE @name NVARCHAR(MAX);
DECLARE @columname NVARCHAR(MAX);
DECLARE @datatype NVARCHAR(MAX);
DECLARE @resultadofinal VARCHAR(2000);
DECLARE @sentencia NVARCHAR(4000);

DECLARE @cuantos INT;
DECLARE @paramdefinition NVARCHAR(500)= N'@nombre nvarchar(200), @cuantosout int output';
DECLARE micursor CURSOR
FOR SELECT t.name,
           s.column_name,
           s.data_type
    FROM information_schema.columns s
         INNER JOIN sys.tables t ON t.name = s.table_name
    ORDER BY t.name;
OPEN micursor;
FETCH micursor INTO @name, @columname, @datatype;
WHILE(@@fetch_status = 0)
    BEGIN
        SET @cuantos = 0;
        BEGIN TRY
            SET @sentencia = 'select @cuantosout = count(*) from [ejemplo2].dbo.['+@name+'] where cast('+@columname+' as nvarchar(4000)) like N''%1234%''; ';
            EXEC sp_executesql
                 @sentencia,
                 @paramdefinition,
                 @name,
                 @cuantosout = @cuantos OUTPUT;
            IF(@cuantos > 0)
                BEGIN
                    PRINT @name;
                    PRINT @columname;
                    PRINT @datatype;
                    PRINT @cuantos;
                END;
        END TRY
        BEGIN CATCH
	
/* tratamiento de excepciones */

        END CATCH;
        FETCH micursor INTO @name, @columname, @datatype;
    END;
CLOSE micursor;
DEALLOCATE micursor;

Generar una query dinámica, bien sea como está el ejemplo sobre sys.tables o sobre las vistas de informationSchema y recorrerlas mediante un cursor, para cada columna.

En el ejemplo se da información sobre la columna y sobre la cantidad de veces que se encuentra la cadena 1234 independientemente del tipo de dato.

Esto es solo una aproximación. Si quieres algo más, tendrás que precisar, sobre todo, si la búsqueda es por cadenas (varchar, nvarchar, ojo con text), etc. Y como resolver la salida de las querys, con identificadores de fila etc.