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?

possible duplicate of [Query to list SQL Server stored procedures along with lines of code for each procedure](http://stackoverflow.com/questions/291574/query-to-list-sql-server-stored-procedures-along-with-lines-of-code-for-each-proc) — Neil Knight, Jan 07 '11 at 18:53

Conrad Frix · Answer 1 · 2011-01-07T22:43:12.827

50

select Count(*) from sys.procedures

And as Philip Kelley noted this is sql 2005 and up

edited Jan 07 '11 at 22:43

answered Jan 07 '11 at 18:40

Conrad Frix

51,984
12
96
155

score 14 · Answer 2 · edited Sep 25 '12 at 07:32

14

-- 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'

edited Sep 25 '12 at 07:32

Kieren Johnstone

41,277
16
94
144

answered Sep 25 '12 at 06:36

Iyyappan

171
1
5

score 10 · Answer 3 · answered Jan 07 '11 at 18:40

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.

score 7 · Accepted Answer · answered Jul 10 '20 at 23:57

7

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_%'

answered Jul 10 '20 at 23:57

phantomflash

114
1
4

SELECT count(*) FROM sys.procedures WHERE SUBSTRING([name], 1, 3) <> 'sp_' for SQL 2019, SSMS 19 – Marc Roussel Oct 12 '22 at 14:16
OR add a backslash before the underscore – Marc Roussel Oct 12 '22 at 14:29

score 3 · Answer 5 · answered Jan 07 '11 at 18:40

3

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

answered Jan 07 '11 at 18:40

Jason

1,325
6
14

score 2 · Answer 6 · answered Jan 07 '11 at 18:39

2

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

answered Jan 07 '11 at 18:39

Lamak

69,480
12
108
116

This gives a count including both system and mine written stored procedure, i want to know count for my own written stored procedure only. – NoviceToDotNet Jan 07 '11 at 18:47
@NoviceToDotNet: That's not what your question asked. – Neil Knight Jan 07 '11 at 18:48
@NoviceToDotNet When you say "mine written", are you talking about the procedures written only by your user?, or any user? – Lamak Jan 07 '11 at 18:50
other then the system procedure, witten by me or any body working on that data base – NoviceToDotNet Jan 07 '11 at 18:53
1

This now is a duplicate question – Neil Knight Jan 07 '11 at 18:54

score 1 · Answer 7 · answered Jan 07 '11 at 18:42

1

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

answered Jan 07 '11 at 18:42

Doliveras

1,794
2
14
30

score 0 · Answer 8 · edited Aug 23 '22 at 00:11

This will give you the count of tables and stored procedures.

SELECT 
    CASE TYPE 
        WHEN 'U' 
            THEN 'User Defined Tables' 
        WHEN 'S'
            THEN 'System Tables'
        WHEN 'IT'
            THEN 'Internal Tables'
        WHEN 'P'
            THEN 'Stored Procedures'
        WHEN 'PC'
            THEN 'CLR Stored Procedures'
        WHEN 'X'
            THEN 'Extended Stored Procedures'
    END, 
    COUNT(*)     
FROM SYS.OBJECTS
WHERE TYPE IN ('U', 'P', 'PC', 'S', 'IT', 'X')
GROUP BY TYPE

You can find in sys.objects all types of objects in the database. You will have to run this query on each of your databases to see the count of objects.

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

8 Answers8