Sometimes when working a team, you want to see if there are some changes have been made in the stored procedures. Whether they have been created new or have been altered recently. If you want to know if there are some changes have already been made, you can use the following simple queries.
The following query will select the top 10 stored procedures that have been created.
SELECT TOP 10 * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo' ORDER BY CREATED DESC
The following query will select the top 10 stored procedures that have been altered.
SELECT TOP 10 * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = N'PROCEDURE' AND ROUTINE_SCHEMA = N'dbo' ORDER BY LAST_ALTERED DESC
How about for the functions? Well they are pretty the same on how to retrieve those information. The only difference is you just need to change the routine type.
The following query will select the top 10 functions that have been created.
SELECT TOP 10 * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo' ORDER BY CREATED DESC
The following query will select the top 10 stored procedures that have been altered.
SELECT TOP 10 * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = N'FUNCTION' AND ROUTINE_SCHEMA = N'dbo' ORDER BY LAST_ALTERED DESC