Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_sql_-_recently_modified_records [2019/01/27 11:29]
control_sql_-_recently_modified_records [2019/01/27 11:29] (current)
Line 1: Line 1:
 +======  ======
 +
 +
 +
 +==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.
 +
 +
 +
 +<code sql>
 +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;
 +</code>
 +
 +
 +
 +===== Version Information =====
 +  * Entered : 08/27/2012
 +  * Version : 1.0
 +
 +
 +