Find string in Stored Procedures (sp_executesql)

Trying to find a view that is called from many stored procedures - need name
of view only. Query below does not work. Do not want to execute?
---
declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
declare mycur cursor for
select name from sysobjects where xtype='P'
open mycur
fetch next from mycur into @.tblname
while @.@.fetch_status=0
begin
select @.sql=N'exec sp_helptext '+@.tblname
EXEC sp_executesql
@.stmt = @.sql,
@.params = N'@.gettext int output',
@.gettext= @.gettext output
if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
print @.tblname
fetch next from mycur into @.tblname
end
close mycur
deallocate mycur
Regards,
JamieYou may want to check out the system table syscomments where the source of
all user procedures are held.
Anith|||http://databases.aspfaq.com/databas...br />
ext.html
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:96C2F1E4-2672-4DCF-BA4B-EAB8227F44CD@.microsoft.com...
> Trying to find a view that is called from many stored procedures - need
> name
> of view only. Query below does not work. Do not want to execute?
> ---
> declare @.tblname varchar(120),@.sql nvarchar(4000),@.gettext varchar(8000)
> declare mycur cursor for
> select name from sysobjects where xtype='P'
> open mycur
> fetch next from mycur into @.tblname
> while @.@.fetch_status=0
> begin
> select @.sql=N'exec sp_helptext '+@.tblname
> EXEC sp_executesql
> @.stmt = @.sql,
> @.params = N'@.gettext int output',
> @.gettext= @.gettext output
> if charindex( 'ATVINVSTANDARDVIEW',upper(@.gettext))>0
> print @.tblname
> fetch next from mycur into @.tblname
> end
> close mycur
> deallocate mycur
>
> --
> Regards,
> Jamie