Friday, July 10, 2015

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



No comments:

Post a Comment