IMG-LOGO

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

andy - 23 Jan, 2018 4119 Views 0 Comment

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.

Related Articles

How to restore database using SQL query in TSQL?

If you have a database backup bak file extension and want to restore it using SQL query You can use the built in RESTORE DATABASE function Remember in order to be able to restore a database successfully You need to ...

How to get all table sizes in TSQL?

To get the information about how much space or size used by tables You can retrieve the size information by linking multiple tables in sys tables There are two tables that hold this information The first one is the sys ...