SQL Server 7.0 / 2000

Buscar Texto en todos los SP de una DB simultáneamente

Por Raúl Alfredo Epstein

Este artículo tiene el propósito de mostrar una manera simple y efectiva de buscar un texto (String) existente en todos los Stored Procedures (en adelante SP) al mismo tiempo en una determinada Base de Datos (en adelante DB).

El método consta de utilizar una SP que busca entre las tablas de sistema de una determinada DB, utilizando un LIKE contra el campo dónde el motor guarda el texto de los SP. Es importante destacar que, obviamente, esto no funciona con los SP encriptados.

¿Cuándo es necesario implementar este método de búsqueda?

Es interesante pensar en este método cuando nos vemos en la necesidad de responder a cuestiones, durante un desarrollo o mantenimiento, como ser

¿Cuáles son los SP que utilizan el campo “intIdCampo” de la tabla “MiTabla”?”

¿Cuáles son los SP que utilizan “LIKE” en su “WHERE”?

¿Cuáles son los SP que utilizan la función “IsNumeric”?

¿Cuáles son los SP que tienen un comentario “—Autor: A.B.C.”?

Y muchas más situaciones parecidas con las que nos topamos diariamente al investigar una DB, analizar cambios, mejoras y demás actividades.

También, este método, nos permite saber cuáles SP están utilizando cierta tabla, útil cuando la tabla en cuestión debe ser renombrada o eliminada, si hacemos una búsqueda por “NombreTabla”. Esto mismo lo podemos obtener desde el EM buscando dependencias pero, esta información no es siempre confiable.

Algoritmo

El algoritmo que aplicaremos para llevar datos de un servidor a otro será el siguiente.

1. Identificamos la cadena o texto a buscar en los SP teniendo como herramientas de búsqueda la restricciones de una búsqueda por LIKE, uso de comodines etc.

2. Ejecutamos el SP

3. Como resultado tenemos los SP que buscábamos para investigarlos en detalle. Nos devuelve resultado en 2 columnas, la primera con los nombres de los SP y la segunda con la instrucción lista para ejecutar de sp_helptext para solicitar al motor el código de cada SP.

Método

1. Debemos tener acceso con permisos para crear el SP y los necesarios en las DB destino que queramos investigar.

2. Debemos ejecutar el script T-SQL que vemos a continuación para crear un Stored Procedure llamado p_FindText

SET QUOTED_IDENTIFIER ON

SET ANSI_NULLS ON

--------------------------------------------

-- OBJECT NAME: p_FindText

-- AUTHOR: Raúl Alfredo Epstein

-- AUTHOR EMAIL: raultuc@hotmail.com

-- DATE: 30/04/2005

-- INPUTS: @strFind -> Cadena a buscar

--@varDBName -> DB en la que se buscará, por defecto en Northwind

-- OUTPUTS: Nombres de SP que contienen la cadena buscada

-- DEPENDENCIES: Ninguna

-- DESCRIPTION:

El método consta de utilizar una SP que busca entre las tablas de sistema

de una determinada DB, utilizando un LIKE contra el campo dónde el motor

guarda el texto de los SP. Es importante destacar que, obviamente, esto no

funciona con los SP encriptados. */

-- MODIFICATION HISTORY:

-------------------------------------------------

-- 30/04/2005 - Raúl Alfredo Epstein

-- Creación.

-------------------------------------------------

CREATE proc dbo.p_FindText

@strFind varchar (100),

@varDBName varchar (100) = 'Northwind'

BEGIN

declare @varQuery varchar (1000)

select @varQuery =

'SELECT distinct ' +

'name SP_Name, ''sp_helptext '''''' + name + ''''''''SP_HT ' +

'FROM [' + @varDBName + '].[dbo].[sysobjects] inner join [' + @varDBName + '].[dbo].[syscomments] ' +

'on [' + @varDBName + '].[dbo].[sysobjects].id = [' + @varDBName + '].[dbo].[syscomments].id ' +

'where xtype = ''P'' ' +

'and text like ''%' + @strFind + '%'' ' +

'order by name '

exec (@varQuery)

END --sp

SET QUOTED_IDENTIFIER OFF

SET ANSI_NULLS ON

Prueba y ejemplo

Una vez creado el SP, esta listo para ejecutarse, por ejemplo con la siguiente cadena de búsqueda (para la DB Northwind):

DECLARE @RC int

DECLARE @strFind varchar(100)

DECLARE @varDBName varchar(100)

SELECT @strFind = 'OrderID'

, @varDBName = 'Northwind'

EXEC @RC = p_FindText @strFind, @varDBName

Dando como resultado:

SP_Name	SP_HT
CustOrderHist	sp_helptext 'CustOrderHist'
CustOrdersDetail	sp_helptext 'CustOrdersDetail'
CustOrdersOrders	sp_helptext 'CustOrdersOrders'
Employee Sales by Country	sp_helptext 'Employee Sales by Country'
Sales by Year	sp_helptext 'Sales by Year'
SalesByCategory	sp_helptext 'SalesByCategory'

(3 filas afectadas)

Y finalmente si ejecutamos para este caso particular:

sp_helptext 'CustOrderHist'

Esto nos da como resultado:

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)

SELECT ProductName, Total=SUM(Quantity)

FROM Products P, [Order Details] OD, Orders O, Customers C

WHERE C.CustomerID = @CustomerID

AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

GROUP BY ProductName