Buscar

viernes, 10 de junio de 2011

Buscar texto en un Store Procedure (SP) de SQL

Este script lo realicé para buscar un texto dentro de la definición de un store procedure (sp) y nos lista los nombres de los Sp que contienen esa palabra

hay que cambiar el texto a buscar en la parte

set @PalabraABuscar = 'Texto a buscar'

Espero lo disfruten :)

------Comienza código --------

SET NOCOUNT ON
DECLARE @PalabraABuscar nvarchar(500)
DECLARE @Encontrado int
DECLARE @Nombre nvarchar(100)
DECLARE @Id int

CREATE TABLE #Tmp_Encontrados(
Nombre nvarchar(500) NULL,
pos int null)

set @PalabraABuscar = 'Texto a buscar'


DECLARE Cursor_Objeto CURSOR FOR
SELECT object_id,name FROM sys.objects WHERE type in (N'P', N'PC')

OPEN Cursor_Objeto;

FETCH NEXT FROM Cursor_Objeto
INTO @Id, @Nombre;

WHILE @@FETCH_STATUS = 0
BEGIN
set @Encontrado=0

SELECT @Encontrado= charindex(@PalabraABuscar,smsp.definition)
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.database_principals AS ssp ON ssp.principal_id = ISNULL(sp.principal_id, (OBJECTPROPERTY(sp.object_id, 'OwnerId')))
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
LEFT OUTER JOIN sys.assembly_modules AS amsp ON amsp.object_id = sp.object_id
LEFT OUTER JOIN sys.assemblies AS asmblsp ON asmblsp.assembly_id = amsp.assembly_id
LEFT OUTER JOIN sys.procedures AS spp ON spp.object_id = sp.object_id
WHERE sp.name=@Nombre

IF @Encontrado > 0
BEGIN
INSERT INTO #Tmp_Encontrados VALUES(@Nombre,@Encontrado)
END

FETCH NEXT FROM Cursor_Objeto
INTO @Id, @Nombre;
END
CLOSE Cursor_Objeto;
DEALLOCATE Cursor_Objeto;


SET NOCOUNT OFF
SELECT * FROM #Tmp_Encontrados
DROP TABLE #Tmp_Encontrados

----Termina Código -------

No hay comentarios:

Publicar un comentario