Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Which Stored Procedure changed Expand / Collapse
Author
Message
Posted Tuesday, May 4, 2010 10:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 11, 2015 12:22 PM
Points: 381, 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.
Post #915480
Posted Tuesday, May 4, 2010 10:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 14,433, Visits: 37,807
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!
Post #915490
Posted Tuesday, May 4, 2010 12:20 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 11, 2015 12:22 PM
Points: 381, Visits: 690
Thanks Lowell, that worked.
Post #915570
Posted Friday, January 18, 2013 10:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 24, 2013 2:35 PM
Points: 4, Visits: 4
Thank you very for providing the information to identify who modified the SP's & sql objects.
Post #1409012
Posted Friday, January 18, 2013 10:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 14,433, Visits: 37,807
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!
Post #1409019
Posted Tuesday, May 3, 2016 6:18 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 19, 2016 1:53 PM
Points: 248, Visits: 1,015
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
Post #1782844
Posted Tuesday, May 3, 2016 6:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 14,433, Visits: 37,807
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!
Post #1782871
Posted Tuesday, May 3, 2016 7:32 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 19, 2016 1:53 PM
Points: 248, Visits: 1,015
Thanks for the reply one more thing, can you join sys.objects on your original script?
Post #1782894
Posted Tuesday, May 24, 2016 12:20 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 19, 2016 1:53 PM
Points: 248, Visits: 1,015
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?
Post #1789404
Posted Tuesday, May 24, 2016 12:28 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:53 PM
Points: 14,433, Visits: 37,807
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!
Post #1789407
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse