Database Stored Procedures list

SELECT o.schema_ID, s.name, o.name, o.create_date, o.modify_date, len(m.[definition]) as 'Definition_length'

FROM sys.schemas s INNER JOIN (sys.sql_modules m inner join sys.objects o on m.object_id=o.object_id) ON s.schema_id = o.schema_id

WHERE type = 'P'

 --and m.[definition] LIKE '%2015%'

 and s.name IN ('[name1]', '[name2]', '[name3]')

 --and s.name NOT IN ('Reporting')

 --and modify_date > '07/01/2015'

order by o.schema_ID, o.name, o.modify_date DESC

SQL Server builds

http://sqlserverbuilds.blogspot.com/

Find all Stored Procedures with specific text included within

In this example, I'm looking for the hard-coded "2015" so it can be replaced with a dynamic variable.

SELECT o.schema_ID, s.name, o.name

   , o.create_date

   , o.modify_date

   , len(m.[definition]) as 'Definition_length'

FROM sys.schemas s INNER JOIN (sys.sql_modules m inner join sys.objects o on m.object_id=o.object_id) ON s.schema_id = o.schema_id

WHERE type = 'P'

 and m.[definition] LIKE '%2015%'

 --and o.name IN ('snapshot', 'snapshotbase', 'snapshotLimit', 'snapshotlimitnonhc', 'snapshot_rel2_model_a','snapshot_rel2_model_b', 'snapshot_rel2_model_c', 'snapshotlimitnonhc')

 and s.name NOT IN ('Reporting')

ORDER BY o.schema_ID, o.name, o.modify_date DESC

Last Run Date of Stored Procedure

SELECT o.name,
       ps.last_execution_time
FROM   sys.dm_exec_procedure_stats ps
INNER JOIN
       sys.objects o
       ON ps.object_id = o.object_id
WHERE  DB_NAME(ps.database_id) = 'HRSSCRPT'
--and o.name like 'sp_gen%'
ORDER BY ps.last_execution_time DESC