Showing posts with label stored procedures. Show all posts
Showing posts with label stored procedures. Show all posts

Tuesday, December 4, 2012

Find instances of a string in all your stored procedures:


This is a great little trick I came across while we were moving from one server to another and had to change the prefix on all references to that SQL machine - but it work in any instance where you need to search through all your stored code:


USE <databasename>;
go
 
SELECT routine_name
              ,routine_definition
FROM information_schema.routines
WHERE routine_definition LIKE '%<my search string>%'
  AND routine_type = 'PROCEDURE'
ORDER BY routine_name;





Wednesday, December 9, 2009

When were the stored procedures last modified?

USE [SOMEdbNAME]
GO
SELECT so.name, so.modify_date
FROM SYS.OBJECTS SO
WHERE SO.name in
   (SELECT ROUTINE_NAME
   FROM INFORMATION_SCHEMA.ROUTINES isr
   WHERE isr.ROUTINE_CATALOG='SOMEdbNAME')
ORDER BY so.modify_date desc

--complements to Igor Malkiman of Qwest Communications for this code