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] (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
 +
 +
 +