Frecuentemente, los desarrolladores y DBAs necesitan buscar objetos o valores dentro de una base de datos. Si alguna vez has tratado de encontrar una función que contuviera una columna específica de una tabla o una variable, o una tabla que contuviera un valor concreto, te habrás dado cuenta que no hay una solución sencilla al estilo del CTRL-F
Ya que no existe una solución nativa en SQL Server Management Studio ni en Visual Studio, os presentamos a continuación un par de opciones útiles para resolver esta situación:
Buscar valores en tablas y vistas
Usar SQL para buscar valores específicos en todas las tablas y todas las columnas de una base de datos está lejos de ser algo sencillo. Existen varios scripts que desde diferentes aproximaciones pueden ser usados para obtener dicha información, pero todos tienen en común el uso de 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;
Las desventajas de esta solución son: el uso de cursors (que normalmente son ineficientes), una alta complejidad y mucho tiempo de ejecución (incluso en bases de datos pequeñas). Otro inconveniente es que solo permite buscar cadenas de texto. Para buscar por otros tipos de datos, como time y datetime, es necesario reescribir el código
Buscando objetos
Buscar un nombre de objeto o la definición de un objeto de base de datos es un poco más sencillo que buscar por un valor específico. Existen multitud de métodos que se pueden usar, aunque todos estos métodos incluyen consultas sobre objetos de sistema
Los siguientes ejemplos buscan por un texto específico (la variable @StartProductID) en los procedimientos almacenados. Cuando se buscan objetos de otro tipo (funciones, triggers, columnas, etc.) o sobre multiples objetos de diferente tipo al mismo tiempo, el script mostrado a continuación deberá ser modificado acorde a esas necesidades.
INFORMATION_SCHEMA.ROUTINES
Usar la vista INFORMATION_SCHEMA.ROUTINES para buscar por un parámetro específico en todos los procedimientos. Esta vista contiene información sobre todos los procedimientos almacenados y funciones de una base de datos. La columna ROUTINE_DEFINITION contiene la instrucción origen que creó dicha 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 se recomienda usar la vista INFORMATION_SCHEMA para buscar esquemas de objetos almacenados en la columna ROUTINE_SCHEMA; en vez de eso es mejor usar la vista de catálogo sys.object.
Vista sys.syscomments
Consultar la vista sys.syscomments, la cual contiene información sobre todo procedimiento almacenado, vista, regla, valor predeterminado, trigger y restricciones de tipo CHECK y DEFAULT de la base de datos. La consulta comprueba la existencia de un valor específico en la columna text, la cual contiene la instrucción DDL
SELECT OBJECT_NAME( id )
FROM SYSCOMMENTS
WHERE text LIKE ‘%@StartProductID%’ AND OBJECTPROPERTY(id , ‘IsProcedure’) = 1
GROUP BY OBJECT_NAME( id );
Y el resultado es
Este método tampoco está recomendado porque la vista sys.syscomments se eliminará en una futura version de SQL Server
Vista sys.sql_modules
Consultar la vista sys.sql_modules que contiene el nombre, tipo y definición de cada modulo de una base de datos
SELECT OBJECT_NAME( object_id )
FROM sys.sql_modules
WHERE
OBJECTPROPERTY(object_id , ‘IsProcedure’)
=
1 AND definition LIKE ‘%@StartProductID%’;
El resultado es el mismo que el de los métodos anteriores
Otras vistas de sistema
Consultar las vistas sys.syscomments, sys.schemas y sys.objects. La vista sys.schemas contiene una fila por cada uno de los esquemas de la base de datos. La vista sys.objects contiene una fila por cada objeto de usuario que tiene asociado un esquema. Hay que tener en cuenta que no contiene información de triggers, por lo que hay que usar la vista sys.triggers para buscar por nombres o definición en estos objetos
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 resultados devueltos son:
El mayor inconveniente de este método es que por cada texto que se desea buscar, es necesario cambiar la instrucción SQL, para lo cual hay que familiarizarse con la estructura de objetos de sistema. Buscar en mútiples objetos de diferentes tipos y añadir criterios de búsqueda adicionales (como incluir o excluir valores en nombres o definición de objetos, o definir el carácter de escape) añade mayor complejidad a la instrucción, lo cual puede dar lugar a errores y a invertir mucho tiempo para validar dicha instrucción.
Si no eres un desarrollador experimentado, preferirás una solución ya probada y libre de errors para buscar objetos y datos de base de datos manualmente, y si no estás familiarizado con objetos de sistema que contienen información DDL de los objetos de base de datos, entonces es mejor usar ApexSQL Search
ApexSQL Search es un add-in para la búsqueda de SQL tanto para SSMS como para Visual Studio. Puede buscar texto dentro de los objetos de la base de datos (incluyendo sus nombres), valores almacenados en tablas y vistas (incluso en las encriptadas) y volver a ejecutar búsquedas previas con un simple click
Para buscar valores en tablas y vistas:
- En SQL Server Management Studio o el menu Main de Visual Studio, hacer click en ApexSQL Search
- 2. Seleccionar Database Text Search…
- En la caja de texto Search, introducir el valor que quieres encontrar
- Del desplegable Database, seleccionar la base de datos sobre la que se quiere buscar
- En el árbol Select objects to search, seleccionar las tablas y vistas sobre las que se quiere buscar, o dejar todo marcado
- Seleccionar si se quiere buscar en vistas o en columnas numéricas, de texto, de tipo uniqueidentifier o fechas, marcando en la casilla correspondiente, y si se quiere una búsqueda exacta o no. Si se busca en campos fecha hay que especificar el format de la misma
- Hacer click en Find now
La lista de resultados se completará con los nombres de las tablas y vistas de la base de datos que contiene el valor introducido
- Hacer click en los puntos suspensivos de Column value para ver el detalle de los objetos encontrados
Para buscar objetos
- En SQL Server Management Studio o el menu Main de Visual Studio, hacer click en ApexSQL Search
- 2. Seleccionar Database Object Search…
- En la caja de texto Search text, introducer el texto que se quiere encontrar (por ejemplo un nombre de variable)
- En el desplegable Database, seleccionar la base de datos en la que se desea buscar
- En el desplegable Objects, seleccionar los tipos de objetos que se quieren buscar, o dejarlo todo marcado
- Seleccionar si se quiere buscar en el objeto, columna, índices, cuerpo del objeto u objetos de sistema marcando la correspondiente casilla, si se quiere usar una búsqueda exacta, e indicar el carácter de escape a usar
- Pulsar Find now
La lista se completará con el nombre de los objetos que contienen el objeto especificado
- Al hacer doble click en la lista Database object search el objeto se seleccionará en el Object Explorer
SQL Server Management Studio y Visual Studio no proporcionan opciones de búsqueda para objetos de base de datos, ni para definiciones de los mismos ni valores. Las consultas SQL que sirven para ese cometido son complejas, lentas y requieren conocimiento de los objetos de sistema de SQL Server. Usa ApexSQL Search para profundizar en tus bases de datos y encontrar de ese modo los valores y objetos que necesitas
– Para más información, http://solutioncenter.apexsql.com/quickly-search-for-sql-database-data-and-objects/#sthash.GxyldGAw.dpuf
