Simular corrupción en base de datos
19 diciembre, 2013
Oferta de Empleo: Consultor .Net /Biztalk
10 junio, 2014

 

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:

  1. En SQL Server Management Studio o el menu Main de Visual Studio, hacer click en ApexSQL Search
  2. 2.       Seleccionar Database Text Search…

 

  1. En la caja de texto Search, introducir el valor que quieres encontrar
  2. Del desplegable Database, seleccionar la base de datos sobre la que se quiere buscar
  3. En el árbol Select objects to search, seleccionar las tablas y vistas sobre las que se quiere buscar, o dejar todo marcado
  4. 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

 

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

 

  1. Hacer click en los puntos suspensivos de Column value para ver el detalle de los objetos encontrados

 

Para buscar objetos

  1. En SQL Server Management Studio o el menu Main de Visual Studio, hacer click en ApexSQL Search
  2. 2.       Seleccionar Database Object Search…

 

  1. En la caja de texto Search text, introducer el texto que se quiere encontrar (por ejemplo un nombre de variable)
  2. En el desplegable Database, seleccionar la base de datos en la que se desea buscar
  3. En el desplegable Objects, seleccionar los tipos de objetos que se quieren buscar, o dejarlo todo marcado
  4. 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
  5. Pulsar Find now

 

La lista se completará con el nombre de los objetos que contienen el objeto especificado

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

 

Compártelo: Share on FacebookTweet about this on TwitterShare on LinkedInPin on Pinterest

Comments are closed.

NEWSLETTER