X7LvPREq

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