How to find list of stored procedures in sql server

This query will display list of stored procedure and tables present in databases with their last modified date / updated date.
It has one parameter where you can change as per your needs like if you want to find out list of trigger then use ‘TR’ for Trigger and ‘FN’ for function in where clause.

List of stored procedure with last modified date 

select name as [Stored Procedure Name],
create_date as [Created Date],
modify_date as [Last Modified Date],
Type_desc as [Object type],
object_name(parent_object_id) as [Dependent Object]
from sys.objects
where type in ( ‘P’,’U’)

Here ‘P’ stands for stored procedure and ‘U’ for user table.
If you have done with your changes then execute the query in selected database or if you want to execute the same query against all database then use below undocumented command ie sp_MSforeachdb

exec sp_MSforeachdb ‘select ”?” select name as [Stored Procedure Name],
create_date as [Created Date],
modify_date as [Last Modified Date],
Type_desc as [Object type],
object_name(parent_object_id) as [Dependent Object]
from [?].sys.objects where type in ( ”U”,”P”,”TR”)’

You can also use cursor instead of using sp_MSforeachdb stored procedure as per your need. Alternative script for finding list of all stored procedure and functions from all database with last modified date

select routine_name as [Sql object Name],
routine_type as [Object Type],
last_altered as [Last Altered],
created as [Created Date]
from information_schema.routines

exec sp_MSforeachdb ‘select ”?” select routine_name as [Sql object Name],
routine_type as [Object Type],
last_altered as [Last Altered],
created as [Created Date]
from [?].information_schema.routines’

 

Leave a Reply

  

  

  


6 − 1 =