Published on: 23 January, 2018

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

Posted by andy | 87 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.
Share your article with us and add your own google adsense account to earn extra money. Plus, you can promote a link back to your site.
Related Articles
Published on: 12 February, 2018

If you have a situation where you want to add a column into a table in SQL Server, it would be a good idea to perform a check first if a column has already existed in a table. Otherwise, if you try to add a duplicate column, you may get an error message when you try to add a column via SQL query.

Published on: 05 February, 2018
If you develop a website or a program that allows your users to perform a search. When the users perform a search query, you probably want to return the best match result first because it is actually more relevant to the search they need. For example, let says we have a database table contains the following list of product.
Published on: 25 September, 2017
When creating a function dynamically, it is a best practice to perform a check if a particular function you want to create has already existed in a database. To perform a check of an existing function you can use the following EXISTS keyword. If the function does not exist then perform a creation of the function.
Published on: 12 May, 2016
Do you need to sort comments with nested replies in SQL Server? Well this can be easily done by combining the same table using the UNION ALL join to populate the comments content. There is a trick to used to sort the comments which use the combination of a record ID field with the row number index.
Published on: 14 April, 2016
Before running an SQL script to create a table dynamically, it would be a good step if you perform a checking if a table already exists. If it does, you may want to skip creating the table otherwise you will get an error exception.
Related Tutorials
Published on: 27 September, 2013
Learn what is UpperCase function in SQL server and how to use UCASE() in sql query.
Published on: 26 September, 2013
Learn what is LCASE() function in SQL server and how to use LCASE() in sql query.
Published on: 25 September, 2013
Learn what is LEN() function in SQL server and how to use LEN() in sql query.
Published on: 24 September, 2013
Learn what is COUNT() function in SQL server and how to use COUNT() in sql query.
Published on: 23 September, 2013
Learn what is AVG() function in SQL server and how to use AVG() in sql query.