mySQL list modified procedures

Recently I had a requirement where I needed to update stored procedure on the server to reflect my local changes and we had a huge number of stored procedure,
so I was trying to list stored procedures which have been modified after a specific date. In this tip I am going to share that SQL query with you. 🙂

mySQL provides a [SHOW PROCEDURE STATUS] query using this we see the Modified column but we can not sort by Modified.
So I created a small query that will list all the stored procedures that have been modified after a specific date. For this I have used [information_schema] DB’s [ROUTINES] table.

INFORMATION_SCHEMA is the information database, the place that stores information about all the other databases that the MySQL server maintains. [1]

SELECT `ROUTINE_NAME`, 
       `CREATED`, 
       `LAST_ALTERED` 
FROM   `information_schema`.`ROUTINES` 
WHERE  `ROUTINES`.`ROUTINE_NAME` LIKE '%sp_xyz%' 
       AND `ROUTINES`.`LAST_ALTERED` > '2015-01-01' 
ORDER  BY `ROUTINES`.`LAST_ALTERED` DESC;

 

Running this query will list out all the stored procedures modified after a specific date and whose name are like ‘%sp_xyz%‘. Hope this saves some time for you.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s