Published on: 23 January, 2018

How to get the latest created or altered stored procedures in SQL Server?

Posted by andy | 338 views 0 likes 0 favourites 0 comments
Tumblr Digg Google Plus

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
Comments

There are no comments available.

Write Comment
0 characters entered. Maximum characters allowed are 1000 characters.