Show pageOld revisionsBacklinksBack to top This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong. CKG Edit ====== ====== ==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