Busqueda de cadena en procedimiento

Ahi van los creditos correspondientes:

Emilio Boucaou
Declare @objname nvarchar(776)
Create table ##Listado (Objname nvarchar(776))

declare Lista cursor for

Select name from sysobjects
where xtype='P'
open lista

Fetch lista into @objname
while @@fetch_status=0
Begin
if exists(Select 1 from syscomments
where id = object_id(@objname) and encrypted= 0
and Patindex(Upper('%ACA COLOCAS LO QUE QUIERES
BUSCAR%'),Upper(text))<>0)
begin
insert ##listado select @objname
end
Fetch lista into @objname
End

Close Lista
Deallocate Lista

Select * from ##listado
drop table ##listado

- Eladio Rincon
select routine_name
from information_schema.routines
where
routine_type = 'PROCEDURE'
and routine_definition like 'tu_texto'

- Justing Xiang
Select name
from sysobjects
where type = 'P'
and id in (Select id from syscomments where text like '%..%')

How do I find a stored procedure containing <text>?
http://www.aspfaq.com/show.asp?id 37


Respecto a las versiones que presentastes, les comento lo siguiente:

(1)

- no hace falta declarar un cursor para lograr eso, tienes toda la info
necesaria en la tabla de sistema syscomments.

(2)

- hacer la busqueda en information_schema.routines no es mala idea, pero
microsoft olvido poner el resto de la definicion cuando los sp tienen mas de
4000 caracteres. Asi que si el texto a buscar esta en una posicion mas alla
de los 4000, usando esta vista no lo vas a encontrar

Ejemplo:

declare @sql varchar(8000)

set @sql = '
create procedure p1
as
'print ''sql server 2000''
return 0
'

exec (@sql)
go

select
routine_name,
routine_definition
from
information_schema.routines
where
routine_type = 'procedure'
and routine_name = 'p1'
go

select
routine_name,
routine_definition
from
information_schema.routines
where
routine_type = 'procedure'
and routine_definition like '%sql server 2000%'
go

select
colid,
object_name([id]) as procedure_name,
[text]
from
dbo.syscomments
where
objectproperty([id], 'IsProcedure') = 1
and object_name([id]) = 'p1'
order by
colid
go

select distinct
object_name([id]) as procedure_name
from
dbo.syscomments
where
objectproperty([id], 'IsProcedure') = 1
and [text] like '%sql server 2000%'
go

drop procedure p1
go

(3)

- no have falta consultar sysobjects para sacar el nombre del procedimiento,
puedes usar object_name([id]) desde la tabla syscomments


AMB

"Isaias" wrote:

Ahi van los creditos correspondientes:

Emilio Boucaou
Declare @objname nvarchar(776)
Create table ##Listado (Objname nvarchar(776))

declare Lista cursor for

Select name from sysobjects
where xtype='P'
open lista

Fetch lista into @objname
while @@fetch_status=0
Begin
if exists(Select 1 from syscomments
where id = object_id(@objname) and encrypted= 0
and Patindex(Upper('%ACA COLOCAS LO QUE QUIERES
BUSCAR%'),Upper(text))<>0)
begin
insert ##listado select @objname
end
Fetch lista into @objname
End

Close Lista
Deallocate Lista

Select * from ##listado
drop table ##listado

- Eladio Rincon
select routine_name
from information_schema.routines
where
routine_type = 'PROCEDURE'
and routine_definition like 'tu_texto'

- Justing Xiang
Select name
from sysobjects
where type = 'P'
and id in (Select id from syscomments where text like '%..%')