lunes, 30 de diciembre de 2013

SQL SERVER: BUSCANDO TEXTO EN TABLAS.

BUSCANDO TEXTO EN... ¿TODAS LAS TABLAS?...

Hace ya algunas semanas -  ¿meses quizá? - me vi en la penosa necesidad de rastrear datos por una base de datos algo grande. El contexto es en sí es irrelevante. Lo interesante era buscar esos datos, un grupo de cadenas de texto y ver con que campos de que tablas se estaban relacionando o los estaban conteniendo para un posterior análisis. En medio de la tarea me topé con un procedimiento almacenado diseñado por Narayana Vyas Kondreddi algo interesante, y funcional.... Pero, había un detalle... El procedimiento busca literalmente en todas las tablas de la base de datos, lo que, es en sí bueno si: 

 1) No tienes la más remota idea de dónde empezar a buscar.
 2) La base de datos es pequeña o a pesar de contener muchas tablas estas no tienen una cantidad abismal de registros.

Para mi bien ya tenía una lista depurada de tablas donde debía de buscar por este grupo de datos. Por lo que procedí a adaptar el procedimiento almacenado en algo que pudiera especificar. El procedimiento original lo pueden encontrar en http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm, en su momento lo adapte para un grupo de tablas específicas y quedo, a modo de ejemplo, el siguiente query:

DECLARE @SearchStr NVARCHAR(100) = 'Bike'

BEGIN
  SET NOCOUNT ON

  DECLARE @TABLAS TABLE (
    Tabla CHAR(45)
    ,N TINYINT IDENTITY
    )
  DECLARE @TMP VARCHAR(20) = NULL
  DECLARE @Ciclos TINYINT = 0

  INSERT INTO @TABLAS
  VALUES ('[dbo].[dimreseller]')

  INSERT INTO @TABLAS
  VALUES ('[dbo].[dimaccount]')

  INSERT INTO @TABLAS
  VALUES ('[dbo].[dimdat]')

  SET @Ciclos = (
      SELECT MAX(N)
      FROM @TABLAS
      )

  IF OBJECT_ID('tempdb..#Results') IS NOT NULL
  BEGIN
    DROP TABLE #Results
  END

  CREATE TABLE #Results (
    ColumnName NVARCHAR(370)
    ,ColumnValue NVARCHAR(3630)
    )

  DECLARE @TableName NVARCHAR(256)
    ,@ColumnName NVARCHAR(128)
    ,@SearchStr2 NVARCHAR(110)

  SET @TableName = ''
  SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%', '''')

  WHILE (@Ciclos > 0)
  BEGIN
    SET @ColumnName = ''
    SET @TableName = (
        SELECT REPLACE(Tabla, ' ', '')
        FROM @TABLAS
        WHERE N = @Ciclos
        )

    WHILE (@TableName IS NOT NULL)
      AND (@ColumnName IS NOT NULL)
    BEGIN
      SET @ColumnName = (
          SELECT MIN(QUOTENAME(COLUMN_NAME))
          FROM INFORMATION_SCHEMA.COLUMNS(NOLOCK)
          WHERE TABLE_SCHEMA = PARSENAME(@TableName, 2)
            AND TABLE_NAME = PARSENAME(@TableName, 1)
            AND DATA_TYPE IN (
              'char'
              ,'varchar'
              ,'nchar'
              ,'nvarchar'
              )
            AND QUOTENAME(COLUMN_NAME) > @ColumnName
          )

      IF @ColumnName IS NOT NULL
      BEGIN
        INSERT INTO #Results
        EXEC (
            'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                           FROM ' + @TableName + ' (NOLOCK) ' + ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
      END
    END

    SET @Ciclos = @Ciclos - 1
  END

  SELECT DISTINCT ColumnName AS [TABLA/COLUMNA]
    ,ColumnValue AS [VALOR]
  FROM #Results

  DROP TABLE #Results

  SET NOCOUNT OFF
END

Funcional en su momento pero nada elegante. Igual, hace unos días recordé el caso y me gustó la idea de crear algo más pulido y que me permitiera contemplar los dos escenarios, es decir, tanto tener la posibilidad de buscar en todas las tablas de la base de datos y también la de especificar un grupo de tablas. Aunque interesante la idea de buscar en toda la base de datos me resulta un poco problemático si el servidor no tiene mucha potencia o si la base de datos es descomunalmente grande. Para bases pequeñas o medianas si me resulta algo practico. En fin, total que al final quedo el siguiente procedimiento almacenado:

CREATE PROCEDURE BuscarTextoEnTablas (
  @TMP NVARCHAR(MAX)
  ,@Print BIT = 0
  )
AS
/*
Autor:Rodrigo Anael Perez Castro | rapcx1981@gmail.com
Creado:30/12/2013 | Ultima Actualizacion:N/A

SINTAXIS:    
-Si se buscara el texto en todas las tablas:
  BuscarTextoEnTablas '<Texto a Buscar>'  Ej: BuscarTextoEnTablas 'A Bike Store',1
-Si se buscara el texto en un grupo especifico de tablas:
  BuscarTextoEnTablas '<Texto a Buscar> IN <Tabla 1>,<Tabla 2>, <Tabla N>' Ej:    BuscarTextoEnTablas 'A Bike Store IN dimreseller,dimaccount,dimdate'
                         
-Opcionalmente se puede especificar 1 despues del texto para indicar si se requiere mostrar el query generado en la pestaña de "Mensajes". Si no se indica nada automáticamente asume que no se requiere.    
                                                    
NOTAS:       -Basado en la idea y trabajo de Narayana Vyas Kondreddi [SearchAllTables/http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm]              
*/
BEGIN
  --VARIABLES DE ENTORNO--------------------------------------------------------
  DECLARE @Query NVARCHAR(MAX) = NULL

  SET @Query = N'
--VARIABLES DE ENTORNO--------------------------------------------------------
DECLARE @Buscar VARCHAR(MAX)=NULL
DECLARE @Ciclos TINYINT=0'
  --REFERENCIAS: http://sqlservercodebook.blogspot.mx/2008/03/check-if-temporary-table-exists.html
  SET @Query = @Query + '
IF OBJECT_ID(''tempdb..#Results'') IS NOT NULL
       BEGIN
             DROP TABLE #Results
       END

CREATE TABLE #Results (ColumnName NVARCHAR(370), ColumnValue NVARCHAR(3630))

SET NOCOUNT ON'

  IF (
      (
        SELECT CHARINDEX('IN', @TMP)
        ) > 0
      )
  BEGIN
    SET @Query = @Query + '
             SET @Buscar=QUOTENAME(''%'' +(SELECT LTRIM(RTRIM(SUBSTRING(''' + @TMP + ''',0,(CHARINDEX(''IN'',''' + @TMP + ''')-1)))))+ ''%'','''''''')'
    --SEPARACION DE CADENA PARA FORMAR TABLA---------------------------------------------------------
    --BASADO EN LA FUNCION: http://emmersonmiranda-net.blogspot.mx/2008/08/generando-mltiples-filas-de-un-string.html
    --Writen By: Emmerson Miranda
    SET @Query = @Query + 'DECLARE @Tablas TABLE (Tabla NVARCHAR(75),N TINYINT IDENTITY)
             DECLARE @Array VARCHAR(MAX)=(SELECT LTRIM(RTRIM((SELECT SUBSTRING(''' + @TMP + ''',(SELECT CHARINDEX(''IN'',''' + @TMP + ''')+2),LEN(''' + @TMP + '''))))))
             DECLARE @SeparatorPosition INT=0
             DECLARE @ArrayValue VARCHAR(MAX)=NULL

             SET @Array = @Array + '',''

             WHILE PATINDEX(''%'' + '','' + ''%'' , @Array) <> 0
             BEGIN
                    SET @SeparatorPosition = PATINDEX(''%'' + '','' + ''%'' , @Array)
                    SET @ArrayValue = SUBSTRING(@Array, 0, @SeparatorPosition)
                    SET @Array = STUFF(@Array, 1, @SeparatorPosition, '''')
                    INSERT INTO @Tablas SELECT ''[dbo].[''+@ArrayValue+'']'' AS Tabla
             END
             '
      --SEPARACION DE CADENA PARA FORMAR TABLA---------------------------------------------------------
  END
  ELSE
  BEGIN
    SET @Query = @Query + '
             SET @Buscar=QUOTENAME(''%'' +(LTRIM(RTRIM(''' + @TMP + ''')))+ ''%'','''''''')'
  END

  SET @Query = @Query + '
             DECLARE @TableName NVARCHAR(256), @ColumnName NVARCHAR(128)
             SET  @TableName = '''''

  IF (
      (
        SELECT CHARINDEX('IN', @TMP)
        ) > 0
      )
  BEGIN
    SET @Query = @Query + '
             SET @Ciclos=(SELECT MAX(N) FROM @Tablas)
             WHILE (@Ciclos>0)'
  END
  ELSE
  BEGIN
    SET @Query = @Query + '
             WHILE @TableName IS NOT NULL'
  END

  SET @Query = @Query + '
             BEGIN
             SET @ColumnName = '''''

  IF (
      (
        SELECT CHARINDEX('IN', @TMP)
        ) > 0
      )
  BEGIN
    SET @Query = @Query + '
             SET @TableName =(SELECT REPLACE(Tabla,'' '', '''')  FROM @Tablas WHERE N=@Ciclos)'
  END
  ELSE
  BEGIN
    SET @Query = @Query + '
             SET @TableName =
             (
                    SELECT MIN(QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME))
                    FROM   INFORMATION_SCHEMA.TABLES
                    WHERE        TABLE_TYPE = ''BASE TABLE''
                           AND    QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME) > @TableName
                           AND    OBJECTPROPERTY(
                                        OBJECT_ID(
                                               QUOTENAME(TABLE_SCHEMA) + ''.'' + QUOTENAME(TABLE_NAME)
                                                ), ''IsMSShipped''
                                               ) = 0
             )'
  END

  SET @Query = @Query + 
    '      
             WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
             BEGIN
                    SET @ColumnName =
                    (
                           SELECT MIN(QUOTENAME(COLUMN_NAME))
                           FROM   INFORMATION_SCHEMA.COLUMNS
                           WHERE        TABLE_SCHEMA = PARSENAME(@TableName, 2)
                                  AND    TABLE_NAME   = PARSENAME(@TableName, 1)
                                  AND    DATA_TYPE IN (''char'', ''varchar'', ''nchar'', ''nvarchar'')
                                  AND    QUOTENAME(COLUMN_NAME) > @ColumnName
                    )
     
                    IF @ColumnName IS NOT NULL
                    BEGIN
                           INSERT INTO #Results
                           EXEC
                           (
                                  ''SELECT '''''' + @TableName + ''.'' + @ColumnName + '''''', LEFT('' + @ColumnName + '', 3630)
                                  FROM '' + @TableName + '' (NOLOCK) '' +
                                  '' WHERE '' + @ColumnName + '' LIKE '' + @Buscar
                           )
                    END
             END'

  IF (
      (
        SELECT CHARINDEX('IN', @TMP)
        ) > 0
      )
  BEGIN
    SET @Query = @Query + '
             SET @Ciclos = @Ciclos-1'
  END

  SET @Query = @Query + '
       END

       SELECT ColumnName AS [TABLA/COLUMNA] , ColumnValue AS [VALOR] FROM #Results
       DROP TABLE #Results
     
SET NOCOUNT OFF'

  IF (@Print = 1)
  BEGIN
    PRINT @Query
  END

  EXECUTE sp_executesql @Query
    ,N'@TMP NVARCHAR, @Print BIT '
    ,@TMP = @TMP
    ,@Print = @Print
END

Pueden verlo con mejor detalle descargando el código desde GitHubEl procedimiento contempla las dos variantes pudiendo buscar en toda la base de datos mandando como argumento simplemente texto a buscar Ej:

BuscarTextoEnTablas 'Datos'

O especificar un conjunto de tablas sobre las que se tiene sospecha Ej:

BuscarTextoEnTablas 'Datos IN Casa,Ciudad,Estado,Pais'

También se puede especificar si se quiere ver como quedo el query en la pestaña de mensaje de SQL Server  mandando el parámetro "1", Ej:

BuscarTextoEnTablas 'Datos IN Casa,Ciudad,Estado,Pais',1

Esto no es particularmente relevante pero tampoco creo que estorbe así que se lo deje como algo adicional. Me gustó la idea de Narayana y creo que tiene más potencial. Espero mejorarla en un futuro no muy lejano. 

Espero les resulte útil. Saludos y por adelantado, que tengan un ¡Feliz Año Nuevo!

REFERENCIAS:



No hay comentarios:

Publicar un comentario

Nota: solo los miembros de este blog pueden publicar comentarios.