February 19, 2008 at 9:33 am
Hi Guys,
Is there a way to know the date/time when a proc was changed?
Is it possible to know the procs that had their code changed in the last week?
Thanks a lot,
Luiz.
February 19, 2008 at 9:47 am
Would the modify_date in sys.objects do the trick?
select top 10 * from sys.objects
where type = 'P'
and modify_date >= '2/01/2008'
February 19, 2008 at 9:57 am
You can utilize the schema changes history report available in SSMS (management studio). Within the SSMS UI, right-click the database and/or server name and select the reports option.
Tommy
Follow @sqlscribeFebruary 19, 2008 at 10:30 am
Thank you for your fast answeres.
Luiz.
February 19, 2008 at 10:53 am
NP 🙂 You can also filter your resultset by querying the trace file directly - i.e.
BEGIN TRY
DECLARE @enable int
SELECT TOP 1 @enable = CONVERT(int,value_in_use) FROM sys.configurations WITH(NOLOCK) WHERE name = 'default trace enabled'
IF @enable = 1 --default trace is enabled
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) collate database_default
, database_name nvarchar(256) collate database_default
, start_time datetime
, event_class int
, event_subclass int
, object_type int
, server_name nvarchar(256) collate database_default
, login_name nvarchar(256) collate database_default
, application_name nvarchar(256) collate database_default
, ddl_operation nvarchar(40) collate database_default
);
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
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, ApplicationName
, 'temp'
FROM ::fn_trace_gettable( @base_tracefilename, default )
WHERE EventClass in (46,47,164) and EventSubclass = 0 and DatabaseID <> 2
AND ObjectName = 'SOMESPNAME'
--AND LoginName <> 'SOMEDOMAIN\SOMEUSER'
--AND LoginName <> 'SOMESQLUSER'
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
, *
FROM @temp_trace WHERE object_type NOT IN (21587)
ORDER BY start_time desc
END
ELSE
BEGIN
SELECT TOP 0 1 AS difference, 1 AS date, 1 AS obj_type_desc, 1 AS obj_name, 1 AS dadabase_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 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
, ERROR_STATE() AS obj_name
, 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 APPLICATION_NAME, 1 AS DDL_OPERATION
END CATCH
Tommy
Follow @sqlscribeFebruary 19, 2008 at 11:40 am
Tommy Bollhofer (2/19/2008)
You can utilize the schema changes history report available in SSMS (management studio). Within the SSMS UI, right-click the database and/or server name and select the reports option.
Where does this report read from? Tell me this information is stored in the DMV's ... that would be awesome.
February 19, 2008 at 11:48 am
From the default trace file (refer to T-SQL in the post above).
Tommy
Follow @sqlscribeViewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply