This post is second part of my previous post about List All Stored Procedure Modified in Last N Days
This post demonstrates the script which displays create date and modify date for any specific stored procedure in SQL Server.
USE AdventureWorks; GO SELECT name, create_date, modify_date FROM sys.objects WHERE type = 'P' AND name = 'uspUpdateEmployeeHireInfo' GO
Now if you run above SP in SQL Server Management studio, you will get following result set. In the result set you can clearly see that it now the query is displaying the name of the stored procedure along with created date as well as modified date. This may be very useful for internal audit as well as if you like to keep watch on your objects.
In recent times at one of the organizations I have noticed that they had created a job to check the stored procedure modification date and if they see changes in the stored procedure date they will create a report based on it. This particular behavior helped the organization to keep track on the object modification and source control.
Leave a comment below this post and tell us if you have seen ever such situation where you have to keep track of the SP modification date?
Reference: Pinal Dave (https://blog.sqlauthority.com)
100 Comments. Leave new
Hello Sabnam,
You can define the default value GETDATE() for create_date column but for modified_date column you will have to implement one out of following two methods:
1. Create update trigger for each table to update the modified_date column.
2. Modify your application to update the modified_date vlaue with each UPDATE statement.
Regards,
Pinal Dave
Hi Pinal,
How to Find Unused Stored procedures in sql server 2005?
Hi Pinal,
How to locate store proecdure when I have more than 15 database in my SQL server and I have not idea that which database is having this Storeprocedure.
I tried . . . Suppose i am using loop and @i variable comes to 9th Database_id ..with database base platform “Master”
Declare @i smallint
SET @i = 9
Declare @Databasename varchar (50)
SET @Databasename = (SELECT Name FROM sys.databases where database_id = @i)
USE @Databasename — Error ‘Incorrect syntax near @databasename’
SELECT name , create_date from sys.procedures where name like ‘%Emails%’
You tell me.. why i am not able to use variable with key word “USE” in SQL.
Secondly, Can you plz give me the way through which I can locate SP on my SQL server and can find out that database having this SP.
One method is to script all the procedures to a file and do find
Another method is running this query
declare @sql varchar(max)
set @sql=”
select @sql=@sql+’ select ”’+name+”’ as database_name from ‘+name+’.sys.objects where name =”spGetAttendanceReport1” union all’ from sys.databases
select @sql=substring(@sql,1,len(@sql)-9)
exec(@sql)
Pinal,
Why is single quote is represented differently when posted here?
Users have to modify the code before running it
Can you change this behaviour?
Hi,
Is there anyway to find out when was the database last accessed/modified in SQL Server 2000?
Tried the below but noluck..
1) select * from master.dbo.sysdatabases
2) select * from master.dbo.sysfiles
3) sp_Helpfile
I didnt look at the last accessed/modified date of the physicall database/log files.
Any help..much appreciated..
Thanks,
Dan
Thank you for this.
One has to wonder why Modified Date is not a column in SQL Server 2005 Management Studio; a rather obvious piece of info to display I should think.
Thanks again,
Matthew
you can use INFORMATION_SCHEMA.ROUTINES catalog view for finding out created date and last modified dates of DB Objects in SQL Server 2005 Management Studio. Like :
SELECT ROUTINE_NAME,ROUTINE_TYPE,CREATED,LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES
Thanks,
Ruchi
@Ruchi,
I have tried that query, and it doesn’t work in 2000. I have modified a procedure to see if the last_altered data changed, and it doesn’t, it keeps showing the create date.
dear all,
i want to know a store procedure for auto generation of a date & time when the a data is added to the table.
can any body knows the procedure
Hi, Pinal
Would you like to display a single stored procedures using
almost all SQL queries.It will be very helpful for me to understand the structure of SP’s as well as their working with queries.
Shipra Agarwal
jaipur.
i want to know a store procedure for auto generation of a date & time when the a data is added to the table.
can any body knows the procedure
Found this from another site – works fine in 2005
SELECT name, modify_date FROM sys.procedures
if you need to run the same against tables then :
SELECT name, modify_date FROM sys.tables
Very nice use ful queries u have posted
Hi,
Is there any way to find which user altered the table last time?
Will it be logged somewhere in SQL Server 2005?
If yes, how to retrieve it?
Thanks,
KB
Hi ,
When ever i create a store procedure like
ALTER PROCEDURE uspAddAddressType
(
@addressTypeName Varchar(30),
@status varchar(30),
@addedOn datetime,
@addedBy varchar(30)
)
AS
BEGIN
INSERT INTO addressType
( addressTypeName,
[status],
addedBy,
addedOn
)
VALUES
( @addressTypeName,
@status,
@addedBy,
GETDATE()
)
END
And Save next time when i see that procedure it display like
ALTER PROCEDURE [dbo].[uspAddAddressType]
(
@addressTypeName Varchar(30),
@status varchar(30),
@addedOn datetime,
@addedBy varchar(30)
)
AS
BEGIN
INSERT INTO addressType
( addressTypeName,
[status],
addedBy,
addedOn
)
VALUES
( @addressTypeName,
@status,
@addedBy,
GETDATE()
)
END
Why it is happening. Any one can help me .
Hi
Dear All,
Already project developed. So there is some changes in the project. So whenever we are altering the procedures it is saving in a separate .sql file but in the same procedure. why it is happening like that. But i want to alter the procedure which is already there in the project.Please solve my problem
SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = ‘P’ and modify_date between ’10-Jan-2011′ and getdate()
Make sure to use unambigious date formats
@Triveni….
its ok……….
thanks FOR this post ….i REALLY NEEDED IT