CODEWITHSUNDEEP
sqlsql-server
NoviceToDotNet
NoviceToDotNet

Reputation: 10815

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

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

Upvotes: 25

Views: 71618

Answers (8)

Prince
Prince

Reputation: 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.

Upvotes: 0

phantomflash
phantomflash

Reputation: 124

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

Upvotes: 8

Iyyappan
Iyyappan

Reputation: 191

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

Upvotes: 16

Conrad Frix
Conrad Frix

Reputation: 52675

select Count(*) from sys.procedures

And as Philip Kelley noted this is sql 2005 and up

Upvotes: 50

Doliveras
Doliveras

Reputation: 1769

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

Upvotes: 1

Jason
Jason

Reputation: 1325

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

Upvotes: 3

Neil Knight
Neil Knight

Reputation: 48547

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.

Upvotes: 10

Lamak
Lamak

Reputation: 70658

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

Upvotes: 2

Related Questions