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
Hi,
can we modify system stored procedure on SQL server 2005 ?
Thanks in Advance
Thanks & Regards
Jayant Dass
declare @day as int
set @day = 12
select * from
(SELECT [name],create_date,modify_date,’table’ type1,1 order1 FROM sys.tables
union
SELECT [name],create_date,modify_date,’view’ type1,2 order1 FROM sys.views
union
SELECT [name],create_date,modify_date,’trigger’ type1,5 order1 FROM sys.triggers
union
SELECT [name],create_date,modify_date,’sp’ type1,3 order1 FROM sys.procedures
WHERE [type] = ‘P’ AND is_ms_shipped = 0 AND [name] NOT LIKE ‘sp[_]%diagram%’
union
select [name],create_date,modify_date,’fn’ type1,4 order1 from sys.objects where type_desc like ‘%function%’)
as modify_table
where datediff(dd,modify_date,getdate())<@day
ORDER BY order1,modify_date DESC
Hi Mukund ,
My Question is little different , can we alter system stored procedure on MSSQL server 2005 ?
Thanks in Advance
Regards
jayant dass
Why do you want to do this?
How do i get the user id of the person who altered store procedure along with modified date in SQL2000/2005/2008?Also how to configure SQL server for keeping the version history of SP changes?
Dear All,
I want to keep the history of any updations take place for any stored procedure ? not only the last modified date.
Suppose a SP ‘ABC’s definition has been modified 10 times from the creation date; i required the data of 10 times updation. If it comes with client machine id; it will be a great helpful to track the activities done on SP.
HI I am trying to create procedure with two columns and need perameter for Bins (if i select 30 then should dispaly 30, if I select 40 then should dispaly 40 ) for bin column. second column display all standard values up to 1000 rows like blow.
Bins standard_normal_Values
1 -0.999325493
2 -0.99367071
3 -0.986924533
4 -0.982329311
5 -0.97467364
6 -0.970499059
7 -0.969153007
8 -0.968298082
9 -0.958441433
10 -0.958200417
11 -0.958079909
12 -0.957231805
13 -0.955901669
14 -0.954814823
15 -0.954814823
16 -0.95192263
17 -0.950481081
18 -0.950360572
19 -0.950360572
20 -0.947120498
21 -0.94448751
22 -0.94448751
23 -0.943175564
24 -0.94031293
25 -0.938766789
26 -0.938055109
27 -0.937936875
28 -0.936749984
29 -0.935681328
30 -0.93107019
-0.928475856
-0.923425887
-0.922020718
-0.921670562
-0.918166734
-0.913744316
-0.910729341
-0.909919891
-0.907375579
-0.906684363
-0.904262834
-0.903917226
-0.901159183
-0.899208317
-0.896231995
-0.895317953
-0.894747245
Can’t you simple pass it as parameter?
where bin=@bin
last update date find problem in sql [email removed]
I noticed that the modify_date on all of my objects is referencing the date we upgraded from SQL 2005 to SQL 2008R2. Is there any other column I could use to determine the last revision date for an object that would not have changed as a result of the upgrade?
Hello,
I want generate all the scripts of the tables and stored procedures modified between two dates. How can I do this?
Thanks
THIS SHOWING SOME ERROR PLEASE DO THE FAVOUR TO SHOOT THIS
CREATE PROCEDURE INCREASE
DECLARE
@INCR DATETIME
AS
@INCR=SELECT MAX(MonthDate) AS MDATE FROM VMONTHDATE
@INCR=@INCR+1
INSERT INTO VMONTHDATE(MonthDate) VALUES (@INCR)
GO
How will i know the SQL Account used to modify the stored procedure?
By default sa user can modify the procedure
Hi,
purchase table sales table
PID PQty PID SQty
1 10 1 5
2 5 2 3
3 7 1 4
1 5
Output Should be
PId PQty SQty Remaining Qty
1 15 9 6
2 5 3 2
3 7 0 7
Can any one help me to get this output
in a single query
select distinct PID
, (select SUM(pqty) from purchase where pid = main.pid) PQty
, isnull((select SUM(sqty) from sales where pid = main.pid), 0) SQty
, (select SUM(pqty) from purchase where pid = main.pid)
– isnull((select SUM(sqty) from sales where pid = main.pid), 0) [Remaining Qty]
from purchase main
Any chance to find out the login id who created the stored procedure ?
I just have to say, that I love your blog. I use it frequently to get information.
Thank you.
Great. This also Working For me too…. Thanks
Hi,
To find the stored procedure details
“use DataBase_Name
go
sp_helptext StoredProcedure_Name”
wil get the Complete Stored Procedure details
Very Vary Helpful …
Thanks SQL Authority
Thanks
Hi,
how to find stored procedure change user detail and column ınformation situation
Thanks
Thank you so much..