Control SQL - Recently Modified Records

Explanation of SQL

This query will pull a list of all records modified recently.

It will return the TableName, ID, ClassTypeID, ModifiedByUser, ModifiedByComputer, and ModifiedDate of the records modified recently.

Risk of Data Corruption if Run Improperly

None. This is a selection query and no data is modified in the running of it.

SQL

Note: As written, this will return all records modified in the past hour. You can change the time frame by changing the value of @HourDiff on the second line.

DECLARE @HourDiff INT;
SET @HourDiff = 1;
 
DECLARE @MinuteDiff INT;
SET @MinuteDiff = @HourDiff * 60;
 
DECLARE @tblName VARCHAR(100);
 
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.TABLES
WHERE is_ms_shipped = 0
ORDER BY name
 
OPEN db_cursor;
 
FETCH NEXT FROM db_cursor 
INTO @tblName
 
CREATE TABLE #Temp_ModRecs(TblName VARCHAR(100), ID INT, ClassTypeID INT, ModifiedByUser VARCHAR(25), ModifiedByComputer VARCHAR(25), ModifiedDate DATETIME);
 
WHILE @@FETCH_STATUS = 0
BEGIN
  BEGIN TRY
    EXEC('INSERT INTO #Temp_ModRecs SELECT ''' + @tblName + ''' AS TblName, ID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate FROM ' + @tblName + ' WHERE DATEDIFF(MINUTE, ModifiedDate, GetDate()) < ' + @MinuteDiff);
  END TRY
  BEGIN CATCH
  END CATCH
 
  FETCH NEXT FROM db_cursor 
  INTO @tblName
END
 
CLOSE db_cursor
DEALLOCATE db_cursor
 
SELECT * 
FROM #Temp_ModRecs
ORDER BY ModifiedDate DESC, TblName
;
DROP TABLE #Temp_ModRecs;

Version Information

  • Entered : 08/27/2012
  • Version : 1.0

Related SQLs