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 -------
Buscar
viernes, 10 de junio de 2011
Suscribirse a:
Entradas (Atom)