What are all the stored procedures in a database in MS SQL Server?

>>warning< This is specific to Microsoft SQL Server 2005

  • You can view the same information in a graphical format with the SQL Server Management Studio.
  1. Database
  2. Select the database
  3. Programmability
  4. Stored procedures

Summary

What are all the stored procedures in a database with MS SQL server 2005?

Stored procedures

use adventureworks;
go

SELECT name AS 'Procedure', SCHEMA_NAME(schema_id) AS 'Schema', 
       type_desc as 'Description', 
       create_date as 'Created', modify_date as 'Modified'
FROM sys.procedures;
GO


Procedure                        Schema            Description           Created                 Modified
-------------------------------- ----------------- --------------------- ----------------------- -----------------------
uspPrintError                    dbo               SQL_STORED_PROCEDURE  2005-10-14 01:58:27.740 2005-10-14 01:58:27.740
uspLogError                      dbo               SQL_STORED_PROCEDURE  2005-10-14 01:58:27.850 2005-10-14 01:58:27.850
uspGetBillOfMaterials            dbo               SQL_STORED_PROCEDURE  2005-10-14 02:00:01.800 2005-10-14 02:00:01.800
uspGetEmployeeManagers           dbo               SQL_STORED_PROCEDURE  2005-10-14 02:00:01.910 2005-10-14 02:00:01.910
uspGetManagerEmployees           dbo               SQL_STORED_PROCEDURE  2005-10-14 02:00:02.020 2005-10-14 02:00:02.020
uspGetWhereUsedProductID         dbo               SQL_STORED_PROCEDURE  2005-10-14 02:00:02.130 2005-10-14 02:00:02.130
uspUpdateEmployeeHireInfo        HumanResources    SQL_STORED_PROCEDURE  2005-10-14 02:00:02.240 2005-10-14 02:00:02.240
uspUpdateEmployeeLogin           HumanResources    SQL_STORED_PROCEDURE  2005-10-14 02:00:02.350 2005-10-14 02:00:02.350
uspUpdateEmployeePersonalInfo    HumanResources    SQL_STORED_PROCEDURE  2005-10-14 02:00:02.460 2005-10-14 02:00:02.460

(9 row(s) affected)
  • Do not forget to first select the database that you want to use.