SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Which Stored Procedure changed


Which Stored Procedure changed

Author
Message
ramadesai108
ramadesai108
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2399 Visits: 690
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.
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70704 Visits: 40924
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!
ramadesai108
ramadesai108
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2399 Visits: 690
Thanks Lowell, that worked.
leela3soft
leela3soft
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 4
Thank you very for providing the information to identify who modified the SP's & sql objects.
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70704 Visits: 40924
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!
Tac11
Tac11
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1485 Visits: 1159
Hi Lowell,
in the result header there are 2 columns 'date' and 'Start_time' what are these can you put specific column name? plus if I want to filter by date like 'modified Store proc date' since 01-01-2016.
Thanks
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70704 Visits: 40924
Tac11 (5/3/2016)
Hi Lowell,
in the result header there are 2 columns 'date' and 'Start_time' what are these can you put specific column name? plus if I want to filter by date like 'modified Store proc date' since 01-01-2016.
Thanks


the columns in the default trace are when the command was executed, and does not have the modified_date of the proc;that is store din the sys.objects table anyway.
even worse, the default trace rolls over with lots of activity; one indexing event, or one problem app that is getting login failed can erase all the history about objects that really changed. a busy system might only have a few hours of results in the trace.

instead, you need to create your own trace, or even better, an extended event that is capturing the DDL changes, so you don't suffer from the truncation/rollover problem.

a query like this would show you what is in the default trace now:

--SELECT * from sys.traces
declare @TraceIDToReview int
declare @path varchar(255)

SET @TraceIDToReview = 1 --this is the trace you want to review! 1=Default Trace
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
T.*
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
WHERE TE.name IN('Object:Created','Object:Altered')
and T.DatabaseName NOT IN('tempdb','ReportServer','ReportServerTempDB')
AND T.StartTime >= DATEADD(dd, DATEDIFF(dd,0,getdate()), -3) --last three days?



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!
Tac11
Tac11
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1485 Visits: 1159
Thanks for the reply one more thing, can you join sys.objects on your original script?
Tac11
Tac11
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1485 Visits: 1159
hi Lowell,
Thanks for the script. Just need help to filter on the columns"
Only columns I wants are:
Date
Object Name
DatabaseName
Starttime
ServerName
User_name
ApplicationName
DDL operation

Can you please post updated script?
Lowell
Lowell
SSC Guru
SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)SSC Guru (70K reputation)

Group: General Forum Members
Points: 70704 Visits: 40924
here's the script with all columns in T.* exploded out to their full name.
you can remove what you don't need easier than i can.
object_name(object_id) has an additional optional parameter, db_id, so you cna add that to your list of columns

object_name(T.[ObjectID],T.[DatabaseID]) i think it goes.


--SELECT * from sys.traces
declare @TraceIDToReview int
declare @path varchar(255)

SET @TraceIDToReview = 1 --this is the trace you want to review! 1=Default Trace
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
object_name(T.[ObjectID],T.[DatabaseID]),
T.[TextData],
T.[BinaryData],
T.[DatabaseID],
T.[TransactionID],
T.[LineNumber],
T.[NTUserName],
T.[NTDomainName],
T.[HostName],
T.[ClientProcessID],
T.[ApplicationName],
T.[LoginName],
T.[SPID],
T.[Duration],
T.[StartTime],
T.[EndTime],
T.[Reads],
T.[Writes],
T.[CPU],
T.[Permissions],
T.[Severity],
T.[EventSubClass],
T.[ObjectID],
T.[Success],
T.[IndexID],
T.[IntegerData],
T.[ServerName],
T.[EventClass],
T.[ObjectType],
T.[NestLevel],
T.[State],
T.[Error],
T.[Mode],
T.[Handle],
T.[ObjectName],
T.[DatabaseName],
T.[FileName],
T.[OwnerName],
T.[RoleName],
T.[TargetUserName],
T.[DBUserName],
T.[LoginSid],
T.[TargetLoginName],
T.[TargetLoginSid],
T.[ColumnPermissions],
T.[LinkedServerName],
T.[ProviderName],
T.[MethodName],
T.[RowCounts],
T.[RequestID],
T.[XactSequence],
T.[EventSequence],
T.[BigintData1],
T.[BigintData2],
T.[GUID],
T.[IntegerData2],
T.[ObjectID2],
T.[Type],
T.[OwnerID],
T.[ParentName],
T.[IsSystem],
T.[Offset],
T.[SourceDatabaseID],
T.[SqlHandle],
T.[SessionLoginName],
T.[PlanHandle],
T.[GroupID]
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value
WHERE TE.name IN('Object:Created','Object:Altered')
and T.DatabaseName NOT IN('tempdb','ReportServer','ReportServerTempDB')
AND T.StartTime >= DATEADD(dd, DATEDIFF(dd,0,getdate()), -3) --last three days?



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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search