How to tell what has changed in a SQL Server Database

CrucisCo
22 Jun 2011
0

One of the things I've had to do recently is work out if objects on a SQL Server had been modified after a particular date. I did some Googling and came up with the following which I've condensed into one simple and easy to read article. Just one caveat before we dive in: these scripts ought to work on all SQL Server versions, but they may not be 100% reliable on versions earlier than SQL Server 2000. In any case you should only take these scripts as indicators and also use other mechanisms to ascertain whether your database objects have been modified. (In other words, ask around!)

Use the following to work out if any sprocs have been created or modified since your target date. You have to run this per database.


DECLARE @datelimit datetime
SET @datelimit = CONVERT(datetime, '15/05/2011', 103)
SELECT SPECIFIC_NAME, CREATED, LAST_ALTERED FROM INFORMATION_SCHEMA.ROUTINES
WHERE LAST_ALTERED > @datelimit

You can get a more thorough result showing all database objects. Once again, run this per database.


DECLARE @datelimit datetime
SET @datelimit = CONVERT(datetime, '15/05/2011', 103)
SELECT name, crdate, refdate FROM sysobjects
WHERE refdate > @datelimit

What about SQL jobs? Try this.


DECLARE @datelimit datetime
SET @datelimit = CONVERT(datetime, '15/05/2011', 103)
EXEC dbo.sp_help_job 
      @date_comparator = '>',
      @date_last_modified = @datelimit,
      @enabled = 0;

Happy SQL-ing!