¿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
GO
SET ANSI_NULLS ON
GO
--------------------------------------------
-- OBJECT NAME: p_FindText
-- AUTHOR: Raúl Alfredo Epstein
-- AUTHOR EMAIL:
[email protected] -- 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'
as
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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
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)
AS
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
