Help

Can anyone help me,
I am writting a script where i get to find out all the
store procedure, triggers,... that has the command "..from
table_name order by 2,1.." for example...
Here is what i got as a base idea... hope you understand
what i am trying to get at:
declare @.i int
declare @.tbnames varchar (150)
set @.i = 1
declare test cursor for
select [name] from sysobjects where xtype = 'U'
open test
fetch next from test into @.tbnames
if @.@.fetch_status = 1
Begin
if @.i < 9
begin
select o.name from syscomments c join sysobjects o
on o.id = c.id
where o.type IN ('TR','TF','P','X','V','FN')
and
c.text like '%from ' + @.tbnames + 'order by' + @.i
+ '%'
set @.i = @.i + 1
end
fetch next from test into @.tbnames
End
close test
deallocate test
Can anyone give me a hand with this pleaseI don't think you need to join from sysobjects just try
select object_name(id),* from syscomments where ctext like '%order by
[0-9]%'
since the comments can be split across multiple rows, IF a row happens to be
split in the middle of the order by OR the sp, etc is encryped your will NOT
get a hit...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"" <anonymous@.discussions.microsoft.com> wrote in message
news:106ea01c41581$4e921ee0$a001280a@.phx
.gbl...
> Can anyone help me,
> I am writting a script where i get to find out all the
> store procedure, triggers,... that has the command "..from
> table_name order by 2,1.." for example...
> Here is what i got as a base idea... hope you understand
> what i am trying to get at:
> declare @.i int
> declare @.tbnames varchar (150)
> set @.i = 1
> declare test cursor for
> select [name] from sysobjects where xtype = 'U'
> open test
> fetch next from test into @.tbnames
> if @.@.fetch_status = 1
> Begin
> if @.i < 9
> begin
> select o.name from syscomments c join sysobjects o
> on o.id = c.id
> where o.type IN ('TR','TF','P','X','V','FN')
> and
> c.text like '%from ' + @.tbnames + 'order by' + @.i
> + '%'
> set @.i = @.i + 1
> end
>
> fetch next from test into @.tbnames
> End
> close test
> deallocate test
> Can anyone give me a hand with this please