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
You could leave a comment if you were logged in.