Which Stored Procedure changed
-
Hi all,
Are there any system stored procedures or anyway to know which user stored procedures changed in the past month? Your help is alway appreciated. I find this forum very helpful and life saving, not to mention time saving.
-
Yes, something like this:
SELECT SPECIFIC_SCHEMA + '.' + SPECIFIC_NAME AS ProcName, LAST_ALTEREDFROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
AND LAST_ALTERED > GETDATE() - 7
John
-
John's solution is nice, because it is using the INFORMATION_SCHEMA views (part of the SQL Standard :), and is portable). But if you prefer SQL Server 2005 (and 2008) system views, you can write the above like:
SELECT schema_name(schema_id) + '.' + name AS ProcName
, modify_date
FROM sys.procedures
WHERE modify_date > GETDATE() - 7
Regards,
Andras
Andras Belokosztolszki, MCPD, PhD
GoldenGate Software -
Thank you John and Andras. When I run the query it shows only newly created Procedures and not altered procedures that were already existing, even when the cirteria includes "LAST_ALTERED".
-
Are the stored procedures being dropped/created and not altered. I think this would render the previous solution invalid.
-
Ideally, you need to implement some sort of object tracking system. A few available options are source control and DDL triggers.
Source control allows you answer the big questions like, what changed, who changed it, and when was it changed. SSMS allows a snap in to some of the bigger source control systems. Go to the menu item Tool --> options --> source control.
DDL triggers give you the ability to track infomation about who drops/alter/creates objects, such as stored procedures. The great thing about this is you can even rollback the transaction and send a message saying dropping stored procedures is not allow.
-
I'm not sure which server version you are using, but in SQL Server 2008 ( including Express ) you can sort the procedures in descending modified date in the Object explorer details view.
Open the stored procedures folder in the Object Explorer Details.
If the Modified Date is not displayed, right click on the 'Name' heading to view the shortcut menu. Check the 'Date Last Modified' option to display it.
Click on the field headings to sort the list of stored procedures.
Cheers
Chieko
-
Regarding the statement that you could see changes in the dates when you used CREATE, but not when you use ALTER, with the queries provided in this tread...
What version of SQL Server are you using? I don't have a place to confirm anymore, but I seem to remember that in SQL 2000 (and maybe 2005), the DateModified value didn't get changed in the system tables when a stored procedure was ALTERed.
Of course, my memory could be way off, too. But it may be worth a quick test.
-
This post was created in March of 2009 and now all of a sudden i am seeing responses. I could not follow ckuroda's instruction on how to see the dates on altered SPs. I am using version 2005.
-
I'm surprised noone mentioned the Default Trace yet, that contains all the recent DDL Changes;
there's a built in report from the default trace in SSMS: shows table drops and created, proc and function drops/alters/creates and more:
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Lowel,
You are showing SQL Express and I am using 2005. I am not seeing a "Report" menu when i right-click on a database. Thanks for your time.
-
Well, my SSMS is actually what gets installed with SQL 2008 standard; it runs the reportagainst any database I point it at;
your SSMS might not have that feature;
anyway, i profiled the report, and this is the command it executes: you could run this to get similar results:
exec sp_executesql @stmt=N'begin try
declare @enable int;
select top 1 @enable = convert(int,value_in_use) from sys.configurations where name = ''default trace enabled''
if @enable = 1
begin
declare @d1 datetime;
declare @diff int;
declare @curr_tracefilename varchar(500);
declare @base_tracefilename varchar(500);
declare @indx int ;
declare @temp_trace table (
obj_name nvarchar(256)
, obj_id int
, database_name nvarchar(256)
, start_time datetime
, event_class int
, event_subclass int
, object_type int
, server_name nvarchar(256)
, login_name nvarchar(256)
, user_name nvarchar(256)
, application_name nvarchar(256)
, ddl_operation nvarchar(40)
);
select @curr_tracefilename = path from sys.traces where is_default = 1 ;
set @curr_tracefilename = reverse(@curr_tracefilename)
select @indx = PATINDEX(''%\%'', @curr_tracefilename)
set @curr_tracefilename = reverse(@curr_tracefilename)
set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + ''\log.trc'';
insert into @temp_trace
select ObjectName
, ObjectID
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, NTUserName
, ApplicationName
, ''temp''
from ::fn_trace_gettable( @base_tracefilename, default )
where EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID = db_id() ;
update @temp_trace set ddl_operation = ''CREATE'' where event_class = 46;
update @temp_trace set ddl_operation = ''DROP'' where event_class = 47;
update @temp_trace set ddl_operation = ''ALTER'' where event_class = 164;
select @d1 = min(start_time) from @temp_trace
set @diff= datediff(hh,@d1,getdate())
set @diff=@diff/24;
select @diff as difference
, @d1 as date
, object_type as obj_type_desc
, (dense_rank() over (order by obj_name,object_type ) )%2 as l1
, (dense_rank() over (order by obj_name,object_type,start_time ))%2 as l2
, *
from @temp_trace where object_type not in (21587) -- don''t bother with auto-statistics as it generates too much noise
order by start_time desc;
end else
begin
Select top 0 1 as difference, 1 as date, 1 as obj_type_desc, 1 as l1, 1 as l2, 1 as obj_name, 1 as obj_id, 1 as database_name, 1 as start_time, 1 as event_class, 1 as event_subclass, 1 as object_type, 1 as server_name, 1 as login_name, 1 as user_name, 1 as application_name, 1 as ddl_operation
end
end try
begin catch
select -100 as difference
, ERROR_NUMBER() as date
, ERROR_SEVERITY() as obj_type_desc
, 1 as l1, 1 as l2
, ERROR_STATE() as obj_name
, 1 as obj_id
, ERROR_MESSAGE() as database_name
, 1 as start_time, 1 as event_class, 1 as event_subclass, 1 as object_type, 1 as server_name, 1 as login_name, 1 as user_name, 1 as application_name, 1 as ddl_operation
end catch',@params=N''
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks Lowell, that worked.
-
Thank you very for providing the information to identify who modified the SP's & sql objects.
-
leela3soft (1/18/2013)
Thank you very for providing the information to identify who modified the SP's & sql objects.glad this post helped you out!
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply