Differences
This shows you the differences between two versions of the page.
— |
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, | ||
+ | |||
+ | |||
+ | |||
+ | ===== Risk of Data Corruption if Run Improperly ===== | ||
+ | |||
+ | **None**. | ||
+ | |||
+ | |||
+ | |||
+ | ===== 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 # | ||
+ | WHILE @@FETCH_STATUS = 0 | ||
+ | BEGIN | ||
+ | BEGIN TRY | ||
+ | EXEC(' | ||
+ | END TRY | ||
+ | BEGIN CATCH | ||
+ | END CATCH | ||
+ | | ||
+ | FETCH NEXT FROM db_cursor | ||
+ | INTO @tblName | ||
+ | END | ||
+ | CLOSE db_cursor | ||
+ | DEALLOCATE db_cursor | ||
+ | SELECT * | ||
+ | FROM # | ||
+ | ORDER BY ModifiedDate DESC, TblName | ||
+ | ; | ||
+ | DROP TABLE # | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Version Information ===== | ||
+ | * Entered : 08/27/2012 | ||
+ | * Version : 1.0 | ||
+ | |||
+ | |||
+ | |||