Count Stored Procedures in Database?

How can i count the number of stored procedures in my database and is it a bad practice to have too many stored procedures?

Answers:

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

Answers:

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.

Answers:

Below is the query you can use:

 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.

Answers:

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

Answers:

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

Answers:

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.

Answers:

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'

Answers:

Here is a simple query you can use:

SELECT COUNT(object_id) FROM sys.procedures

Answers:

With this query, you'll get the data amount of the actual text in all the procs in the DB. The results is in bytes, so do the math for a MB results (1024*1024). divide it by 1048576

SELECT SUM(DATALENGTH(sm.definition))/1048576 as 'Total Proc text size'
FROM sys.sql_modules sm
INNER JOIN sys.sysobjects so ON sm.object_id = so.id
WHERE so.type = 'P' 

Answers: