Cómo buscar rápidamente datos y objetos de bases de datos SQL

Frecuentemente, desarrolladores y administradores de bases de datos necesitan buscar datos u objetos de bases de datos. Si usted alguna vez buscó una función de base de datos que contenía una columna de tabla específica o un nombre de variable, o una tabla que contenía datos específicos, seguramente notó que no hay una solución de un clic, como Ctrl + F.

Como no hay una solución por defecto en SQL Server Management Studio ni en Visual Studio, aquí están un par de opciones que puede usar:

Buscando datos en tablas y vistas

Usar SQL para buscar datos específicos en todas las tablas y todas las columnas de una base de datos está lejos de ser una solución óptima. Hay varios scripts SQL con diferentes enfoques que pueden ser usados para obtener esta información, lo que tienen en común es que todos ellos usan cursores y objetos de sistema.

DECLARE
   @SearchText varchar(200),
   @Table varchar(100),
   @TableID int,
   @ColumnName varchar(100),
   @String varchar(1000);
--modify the variable, specify the text to search for SET @SearchText = 'John';
DECLARE CursorSearch CURSOR
    FOR SELECT name, object_id
        FROM sys.objects
      WHERE type = 'U';
--list of tables in the current database. Type = 'U' = tables(user-defined) OPEN CursorSearch;
FETCH NEXT FROM CursorSearch INTO @Table, @TableID;
WHILE
       @@FETCH_STATUS
       =
       0
    BEGIN
        DECLARE CursorColumns CURSOR
            FOR SELECT name
                  FROM sys.columns
                WHERE
                       object_id
                       =
                       @TableID AND system_type_id IN(167, 175, 231, 239);
        -- the columns that can contain textual data        
--167 = varchar; 175 = char; 231 = nvarchar; 239 = nchar        
OPEN CursorColumns;
        FETCH NEXT FROM CursorColumns INTO @ColumnName;
        WHILE
               @@FETCH_STATUS
               =
               0
            BEGIN
                SET @String = 'IF EXISTS (SELECT * FROM '
                            + @Table
                            + ' WHERE '
                            + @ColumnName
                            + ' LIKE ''%'
                            + @SearchText
                            + '%'') PRINT '''
                            + @Table
                            + ', '
                            + @ColumnName
                            + '''';
                EXECUTE (@String);
                FETCH NEXT FROM CursorColumns INTO @ColumnName;
            END;
        CLOSE CursorColumns;
        DEALLOCATE CursorColumns;
        FETCH NEXT FROM CursorSearch INTO @Table, @TableID;
    END;
CLOSE CursorSearch;
DEALLOCATE CursorSearch;

Los inconvenientes de esta solución son: el uso de cursores, los cuales son generalmente ineficientes, la alta complejidad, la necesidad de mucho tiempo de ejecución, incluso en bases de datos pequeñas. Otra desventaja es que puede ser usado para buscar datos de texto solamente. Para buscar otros tipos de datos, como tiempo o fechas, usted necesita escribir código nuevo.

Buscando objetos

Buscar un nombre de objeto de base de datos o una definición de objeto es un poco más fácil que buscar un texto específico. Hay muchos métodos que usted puede usar. De todas maneras, todos estos métodos incluyen hacer consultas a objetos de sistema.

Los siguientes ejemplos SQL buscan un texto específico – la variable @StartProductID – en procedimientos almacenados. Cuando se buscan objetos en otros tipos de objetos de base de datos – funciones, gatillos, columnas, etc., o en múltiples tipos de objetos de bases de datos al mismo tiempo, el SQL mostrado arriba debería modificarse en consonancia.

INFORMATION_SCHEMA.ROUTINES

Use un SQL que consulta la vista INFORMATION_SCHEMA.ROUTINES para buscar un parámetro específico en todos los procedimientos. La vista INFORMATION_SCHEMA.ROUTINES contiene información acerca de todos los procedimientos almacenados y funciones en una base de datos. La columna ROUTINE_DEFINITION contiene las declaraciones fuente que crearon la función o procedimiento almacenado.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%@StartproductID%' 
    AND ROUTINE_TYPE='PROCEDURE'

Y el resultado es:

No es recomendado usar vistas INFORMATION_SCHEMA para buscar esquemas de objetos almacenados en la columna ROUTINE_SCHEMA. Use la vista de catálogo sys.objects en su lugar.

La vista sys.syscomments

Haga una consulta a la vista sys.syscomments, que contiene información acerca de cada procedimiento almacenado, vista, regla, default, gatillo, y restricciones CHECK y DEFAULT en una base de datos. La consulta revisa un texto específico en la columna text, que contiene el objeto DDL.

SELECT OBJECT_NAME( id )
  FROM SYSCOMMENTS
  WHERE text LIKE '%@StartProductID%' AND OBJECTPROPERTY(id , 'IsProcedure') = 1
  GROUP BY OBJECT_NAME( id );

El resultado es

Este método no es recomendad porque la table sys.syscomments será removida en las futuras versiones de SQL Server.

La vista sys.sql_modules

Haga una consulta a la vista sys.sql_modules la cual contiene el nombre, tipo y definición de cada módulo en una base de datos.

SELECT OBJECT_NAME( object_id )
  FROM sys.sql_modules
WHERE
       OBJECTPROPERTY(object_id , 'IsProcedure')
       =
       1 AND definition LIKE '%@StartProductID%';

Los resultados son los mismos que en el método anterior.

Otras vistas de esquemas sys

Haga una consulta a las vistas sys.syscomments, sys.schemas y sys.objects. La vista sys.schemas contiene una fila para cada esquema de base de datos. La vista sys.objects contiene una fila para objeto definido por usuario y con alcance de esquema en una base de datos. Note que no contiene la información de gatillos, así que usted tiene que usar la vista sys.triggers para buscar nombres de objetos o definiciones de objetos en gatillos.

DECLARE
 @searchString nvarchar( 50 );
SET@searchString = '@StartProductID';
SELECT DISTINCT
    s.name AS Schema_Name , O.name AS Object_Name , C.text AS Object_Definition
FROM
     syscomments C INNER JOIN sys.objects O
                     ON
     C.id
     =
     O.object_id
                   INNER JOIN sys.schemas S
                   ON
     O.schema_id
     =
     S.schema_id
WHERE
    C.text LIKE
     '%'
   + @searchString
   + '%'
 OR O.name LIKE
     '%'
   + @searchString
   + '%'
ORDER BY
       Schema_name , Object_name;

Los resultado retornados son:

La principal desventaja de estos métodos es que por cada cambio en los tipos de objetos buscado usted necesita cambiar la consulta SQL. Para hacer eso, usted necesita conocer bien la estructura de objetos de sistema de tal manera que los pueda modificar. Buscar en múltiples tipos de objetos y añadir criterios de búsqueda adicionales, como incluir/excluir nombres de objetos y cuerpos, o definir el carácter de escape, trae incluso más complejidad al SQL, el cual es propenso a errores sin las pruebas apropiadas, lo cual consume tiempo.

Si usted no es un desarrollador experimentado, prefiere una solución probada y sin errores para buscar objetos SQL y datos manualmente, y no está familiarizado con los objetos de sistema que contienen información DDL acerca de objetos de base de datos, use ApexSQL Search.

ApexSQL Search es un complemento de búsqueda SQL para SSMS y Visual Studio. Puede buscar texto dentro de objetos de bases de datos (incluyendo nombres de objetos), datos almacenadas en tablas y vistas (incluso encriptadas) y repetir búsquedas previas con un solo clic.

Para buscar datos en tablas y vistas:

1. En el menú principal de SQL Server Management Studio o Visual Studio, haga clic en ApexSQL Search
2. Seleccione la opción Database text search…:

   

3. En el campo Search ingrese el valor del dato que desea buscar
4. En el menú Database seleccione la base de datos en la que buscará
5. En el árbol Select objects to search seleccione las tablas y vistas en las que buscará, o déjelas todas seleccionadas.
6. Seleccione si buscar en vistas, números, texto, identificadores únicos y columnas de fechas seleccionando los checkboxes correspondientes, y si buscar por una coincidencia exacta. Si busca en columnas de fechas, especifique el formato de fecha:
   

   

7. Haga clic en la opción Find now. La cuadrícula será llenada con las tablas de la base de datos y las vistas que contienen el valor ingresado:

   

8. Haga clic en el botón Column value para ver los detalles del objeto encontrado:

   

Para buscar objetos:

1. En el menú principal de SQL Server Management Studio o Visual Studio, desde el menú de ApexSQL, haga clic en ApexSQL Search.
2. Seleccione la opción Database object search…:

   

3. En el campo Search text ingrese el texto que desea buscar (por ejemplo, un nombre de variable)
4. En el menú Database, seleccione la base de datos en la cual buscar
5. En la lista Objects, seleccione los tipos de objetos en los que buscar, o déjelos todos seleccionados.
6. Selecciones si buscar en objetos, columnas, nombres de índices, cuerpos de objetos, objetos de sistema, seleccionando los checkboxes correspondientes, si desea buscar una coincidencia exacta y qué carácter de escape usar
7. Haga clic en la opción Find now:
   

   

   La cuadrícula será llenada con los objetos de base de datos que contienen el objeto especificado.

8. Haga doble clic en la cuadrícula Database object search y será resaltada en el Object Explorer
   

   

SQL Server Management Studio y Visual Studio no proporcionan opciones de búsqueda para un nombre de objeto de base de datos, definición de objeto y datos. Las consultas SQL para buscar estas cosas son complejas, lentas y requieren conocimiento de los objetos de sistema de SQL Server. Use ApexSQL Search para buscar en sus bases de datos y encontrar los objetos y datos que necesita.

Traductor: Daniel Calbimonte