MS SQL: How to find out how many stored procedures?

Question

How do you find out how many store procedures that you have in the database and is it a bad practice to have too many store procedure? how many are too many?

Answer 1

Select count(*) from sysobjects where xtype = 'P'

Answer 2

You need as many as your application requires. Keeping procedures small and simple is a good thing.

If you're using sql 2005, a visual way to see your proc count is to navigate in SSMS to your stored procedure node under programmability. Click View-Object Explorer Details and a count of objects will be displayed on the right.

Answer 3

select count(*) from sysobjects where xtype='P'

If all your db access is mandated to be through SP's, then the sky is the limit.

Answer 4

Select count(1) from information_schema.routines
where routine_type = 'PROCEDURE'

Answer 5

you may want to exclude system stored procedures from your count

One way would be to call:

select count(*) from sysobjects 
    where xtype = 'P'
    and category = 0
    and left(name,2) <> 'sp'

That is assuming that you do not prefix your procedures with sp

Answer 6

If you use them, then there really is no such thing as "too many".

I had a developer concerned his stored procs were using too much space in the database, so I had to actually calculate the total SIZE of all stored procs. Good answer for that here. The upshot was that the size of the stored procs together was responsible for maybe .001% of the total size of the database.

Answer 7

This will exclude the ones that sometimes get created when using the diagramming tool (they will start with dt_)

SELECT * FROM information_schema.routines
WHERE OBJECTPROPERTY(OBJECT_ID(SPECIFIC_NAME),'IsMSShipped') =1
AND  routine_type = 'PROCEDURE'