Solved

query only user procs in sql 2000

Posted on 2011-03-08
5
225 Views
Last Modified: 2012-05-11
how can you filter for user created procs in sql 2000?
0
Comment
Question by:anushahanna
  • 3
5 Comments
 
LVL 6

Author Comment

by:anushahanna
ID: 35077228
the query i have is

select name from syscomments a,sysobjects b where a.id = b.id and xtype = 'p' and text like '%something%'
and it brings a lot of records, more than what we created (duplicates too)
0
 
LVL 32

Assisted Solution

by:ewangoya
ewangoya earned 175 total points
ID: 35079051

this is much cleaner

select ROUTINE_NAME
from INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME LIKE '%something%
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35082506
most procs will be more than 4000 char- so i thought of using syscomments..
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 325 total points
ID: 35082821
your query brings duplicates, because when a stored procedure is, when longer, "split" into several rows in syscomments... and if several part are matching ...

apart from that, there is no "system" stored procedure flag, so you cannot "filter" for such things.

select name 
from syscomments a,sysobjects b where a.id = b.id and xtype = 'p' and text like '%something%'
group by name

Open in new window

0
 
LVL 6

Author Comment

by:anushahanna
ID: 35083670
thanks for confirming.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now