Retrieve the number of Stored procedures in the Database

To find the total number of stored procedures:
Sysobjects table will store all the system objects and their properties. We can identify the system objects using the column XTYPE.
Below is the query used to retrieve the total number of stored procedures available
in the database.

Code Snippet

Use databasename Select Count(*) from Sysobjects where xtype='P'

The above query will fetch all system related and user related stored procedures in
a database. If we need only user related stored procedures please proceed with the below query.

Code Snippet

Select * from Sysobjects where xtype='P' AND ID>1

Similarly to retrieve the triggers in the database, we can use the below query.

Code Snippet

Select * from Sysobjects where xtype='TR'

Happy Learning!!!

Regards,

Venkatesan Prabu . J