none
Buscar un dato en TODAS las tablas de una Base de Datos RRS feed

  • Pregunta

  • 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

    miércoles, 12 de agosto de 2020 14:28

Todas las respuestas

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



    • Editado Javi Fernández F jueves, 13 de agosto de 2020 3:17 Insertar las imágenes de salida
    • Propuesto como respuesta Miriam Pasion sábado, 21 de noviembre de 2020 9:27
    miércoles, 12 de agosto de 2020 15:08