Filtering list of stored procedures

Question

Post reply

Filtering list of stored procedures

Dinuj Nath-203580

SSC Veteran

Points: 207
More actions
April 5, 2005 at 3:24 am

#66174

Hi,
I can get list of stored procedures using
select * from information_schema.routines.
How can I filter out the system procedures.
Thanks
Dinuj
Thanks and Regards,
Dinuj Nath

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Something along these lines should work:

select * from information_schema.routines

where

objectproperty(object_id(routine_name),'IsMSShipped')=0

and routine_name not like 'r_%'

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dinuj Nath-203580 SSC Veteran Points: 207 More actions · Answer 2

Thanks Frank, but why "routine_name not like 'r_%'" is needed? Why is the first where clause not enough?

Thanks and Regards,

Dinuj Nath

Dinesh Asanka SSChampion Points: 11058 More actions · Answer 3

select * from information_schema.routines where Specific_Name not like 'dt%'

My Blog: http://dineshasanka.spaces.live.com/

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

I've added this, since I am not interested in those procedures. It's not actually needed. If you don't use it, you'll get the same result as Dinesh.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Sailesh Mishra SSC Rookie Points: 26 More actions · Answer 5

Try this from sysobjects ( another alternative )-

SELECT *

FROM sysobjects

WHERE xtype = 'p'

AND category != 2

ORDER BY name

~~~~~~~~~~~~
Sailesh Mishra

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Sure, this works, but you know that you should prefer using the INFORMATION_SCHEMA views instead of direct querying the system tables if you can?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Dinuj Nath-203580 SSC Veteran Points: 207 More actions · Answer 7

Thanks everyone. Frank, You mean to say there are some system procedures which begin with 'r_'. Will Dinesh's solution work? I mean shouldn't it be 'dt_%' not 'dt%' or is it that anything that starts with 'dt' is considered system procedure. I had figured out the 'dt_%' part, that is why I posted this question on the first place.

Thanks

Dinuj

Thanks and Regards,

Dinuj Nath

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Dinesh's solution will work.

dt% implies dt_%. So as long as your own naming convention doesn't use dt as beginning, it really shouldn't matter what you use.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]