How to count all stored procedures in the SQL Server for a database?

Question

How can I count all stored procedures which are written by me in my database?

Answer 1

select Count(*) from sys.procedures

And as Philip Kelley noted this is sql 2005 and up

Answer 2

-- Information about table -- 
SELECT * FROM sys.sysobjects WHERE xtype = 'U'

-- Information about Stored Procedure --
SELECT * FROM sys.sysobjects WHERE xtype = 'P'

-- Information about Functions --
SELECT * FROM sys.sysobjects WHERE xtype = 'FN'

-- Information about Views --
SELECT * FROM sys.sysobjects WHERE xtype = 'V'

Answer 3

To get the Stored Procedure count:

SELECT COUNT(*) SPCOUNT 
  FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE='PROCEDURE'

or:

SELECT COUNT(*)
  FROM sys.procedures

or:

SELECT COUNT(*) 
  FROM sys.sysobjects
 WHERE xtype = 'P'

Hope one of these help.

Answer 4

As the OP pointed out in a comment, all of the earlier answers are wrong, because they include system procedures. He specifically asked for procedures that were "written by me" -- and later clarified in another comment "other than the system procedure, written by me or anybody working on that data base."

So to exclude system procedures, the only differentiating field I see in sys.procedures is the name. Therefore you need to add a WHERE clause to any of the other answers, like this:

select count(*) from sys.procedures
where name not like 'sp_%'

Answer 5

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'

Answer 6

select count(name)
from sys.objects
where type = 'P'

Answer 7

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