Imagine you had a SQL 2000 system where a developer thought it was a good idea to put random stored procedures in the master database. Now imagine that the same developer was extra creative and decided to modify system stored procedures and not tell anybody.
The sysobjects table has a column that tells you when an object was created, but unfortunately it doesn’t tell you when it was last modified.
So to find the objects which have been modified, you will need to extract them and compare them to a virgin system.
Generating a SQL script isn’t as intuitive as you might think – in fact you can’t script system objects using Enterprise Manager.
Here’s a snippet which will build a script to extract the actual text of all the system stored procedures
select ' exec sp_helptext ''' + name + '''' from sysobjects
where xtype='P'
order by name
If you run this, it will build a script similar to the following
exec sp_helptext 'sp_ActiveDirectory_Obj'
exec sp_helptext 'sp_ActiveDirectory_SCP'
exec sp_helptext 'sp_ActiveDirectory_Start'
exec sp_helptext 'sp_add_agent_parameter'
exec sp_helptext 'sp_add_agent_profile'
exec sp_helptext 'sp_add_data_file_recover_suspect_db'
exec sp_helptext 'sp_add_log_file_recover_suspect_db'
exec sp_helptext 'sp_add_log_shipping_alert_job'
exec sp_helptext 'sp_add_log_shipping_primary_database'
exec sp_helptext 'sp_add_log_shipping_primary_secondary'
exec sp_helptext 'sp_add_log_shipping_secondary_database'
exec sp_helptext 'sp_add_log_shipping_secondary_primary'
exec sp_helptext 'sp_addalias'
... and so on
Run the resulting script on the modified system and a vanilla system, and compare the results in windiff.
From here you will find your modified procs pretty quickly.