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.

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

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;
  • Entered : 08/27/2012
  • Version : 1.0
You could leave a comment if you were logged in.