Query that returns list of all Stored Procedures in an MS SQL database

 

How to Get List of all Stored Procedures in an MS SQL database.

Solution 1

select *  from YourDatabaseName.information_schema.routines 
 where routine_type = 'PROCEDURE'

Solution 2

select *   from YourDatabaseName.information_schema.routines 
 where routine_type = 'PROCEDURE' 
   and Left(Routine_Name, 3) NOT IN ('sp_', 'xp_', 'ms_')

Note: retrun Prodecdure Name Not Start from ‘sp_’, ‘xp_’, ‘ms_’

Solution 3

SELECT name, type   FROM dbo.sysobjects
 WHERE (type = 'P')

 

Microsoft introduced a whole swathe of system catalogue views that can be used for this kind of query.
To get a list of stored procedures, one would simply select from the sys.procedures catalogue view
ie.

select name
from sys.procedures
order by name

Other useful catalogue views are

sys.tables<br />
sys.indexes<br />
sys.indexcolumns<br />
sys.foreign_keys<br />
sys.types<br />

 

Source: https://www.codeproject.com/Tips/865795/Query-that-returns-list-of-all-Stored-Procedures-i

 

Leave a comment