USE master
GO
IF OBJECT_ID('sp_DBHasChanged') IS NULL EXEC ('CREATE PROCEDURE sp_DBHasChanged AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_DBHasChanged
@DatabaseName VARCHAR(256) = NULL,
@StartDateTime DATETIME = NULL,
@EndDateTime DATETIME = NULL,
@ChangeCount INT OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @DatabaseName IS NULL
OR @StartDateTime IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_DBHasChanged',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END
SELECT @ChangeCount = NULL,
@EndDateTime = ISNULL(@EndDateTime, GETDATE())
IF DB_ID(@DatabaseName) IS NULL
RETURN
IF (SELECT CONVERT(INT, value_in_use)
FROM sys.configurations (NOLOCK)
WHERE name = 'default trace enabled') = 1
BEGIN
BEGIN TRY
DECLARE @curr_tracefilename VARCHAR(500) ;
DECLARE @base_tracefilename VARCHAR(500) ;
DECLARE @indx INT ;
SELECT @curr_tracefilename = PATH
FROM sys.traces (NOLOCK)
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' ;
SELECT @ChangeCount = COUNT(*)
FROM ::
FN_TRACE_GETTABLE(@base_tracefilename, DEFAULT) t
INNER JOIN sys.trace_events TE (NOLOCK) ON T.EventClass = TE.trace_event_id
WHERE DatabaseID = DB_ID(@DatabaseName)
AND StartTime >= @StartDateTime
AND StartTime < @EndDateTime
AND te.Name IN ('Object:Created', 'Object:Altered', 'Object:Deleted')
END TRY
BEGIN CATCH
END CATCH
END
GO
EXEC sys.sp_ms_marksystemobject sp_DBHasChanged
GO