GX3RMA4T

1USE AIRPORT;
2 
3/* Declare local variables and drop temp table if it exists. */
4 
5IF CHARINDEX('2016',@@VERSION) > 0
6BEGIN
7 
8    DROP TABLE IF EXISTS #logrecords;
9 
10END;
11ELSE
12BEGIN
13 
14    IF OBJECT_ID('tempdb..#logrecords') IS NOT NULL
15    BEGIN
16 
17        DROP TABLE #logrecords;
18 
19    END
20END
21 
22/* Declare local variables */
23DECLARE @tranname NVARCHAR(66);
24DECLARE @tranid NVARCHAR(28);
25DECLARE @loopcount INT = 1;
26DECLARE @looplimit INT;
27 
28/* Set @tranname to the value you are looking for
29   This works for CREATE/ALTER VIEW, CREATE TABLE, and ALTER TABLE
30   Currently researching other possibilities */
31SELECT @tranname = 'CREATE TABLE';
32 
33/* Get all log records associated with the transaction name specified
34   The results contain a row number per transaction, so all occurrences
35   of the transaction name will be found */
36SELECT  ROW_NUMBER() OVER(PARTITION BY [Transaction ID] ORDER BY [Current LSN]) AS Row,
37    [Current LSN], [Transaction ID], [Transaction Name], operation, Context, AllocUnitName, AllocUnitId, PartitionId, [Lock Information]
38INTO #logrecords
39FROM fn_dblog(NULL,NULL)
40WHERE [Transaction ID] IN
41    (SELECT [Transaction ID]
42    FROM fn_dblog(NULL,NULL)
43    WHERE [Transaction Name] = @tranname);
44 
45SELECT @looplimit = COUNT(*) FROM #logrecords
46WHERE [Transaction Name] = @tranname;
47 
48/* The object id for the object affected is contained in the [Lock Information] column of the second log record of the transaction
49   This WHILE loop finds the second row for each transaction and does lots of string manipulation magic to return the object id
50   from a string like this:
51   HoBt 0:ACQUIRE_LOCK_SCH_M OBJECT: 9:146099561:0
52   Once it finds it, it returns the object name */
53WHILE @loopcount <= @looplimit
54BEGIN
55 
56    SELECT TOP 1 @tranid = [Transaction ID]
57    FROM #logrecords
58    DECLARE @lockinfo NVARCHAR(300);
59    DECLARE @startingposition INT;
60    DECLARE @endingposition INT;
61    SELECT @lockinfo = REVERSE([Lock Information]), @startingposition = (CHARINDEX(':',REVERSE([Lock Information])) + 1), @endingposition = CHARINDEX(':',REVERSE([Lock Information]),(CHARINDEX(':',REVERSE([Lock Information])) + 1))
62    FROM #logrecords
63    WHERE Row = 2
64    AND [Transaction ID] = @tranid;
65 
66    SELECT OBJECT_NAME(REVERSE(SUBSTRING(@lockinfo,(@startingposition),(@endingposition - @startingposition)))) AS ObjectName, (SELECT [Transaction SID] from fn_dblog(NULL,NULL) dblog where dblog.[Transaction ID] = @tranid and [Transaction SID] is not null) as [user_SID];
67 
68    DELETE FROM #logrecords
69    WHERE [Transaction ID] = @tranid;
70 
71    SELECT @loopcount += 1;
72 
73END