Don’t know who dropped the tables in your database, Here you go

Hi Folks – Before some time I came to a situation where somebody dropped so many tables from our database and we had a hard time to find the details. This article will tell you that what were the tables that were dropped and who dropped them.  To illustrate this I’m taking a below example :

/* Use an existing db OR create new one, I’m using existing one */

Use Test_1

/* Lets create a test table */

 Create table Test_Table(empID int, name nvarchar(20))


/* Insert values in that table */

 insert into Test_Table values (100,’Gaurav’)
insert into Test_Table values (101,’Ashish’)
insert into Test_Table values (102,’Vivek’)


/* Check the data in that table */

select * from Test_Table


/* Note down the Object Id of that table */

SELECT OBJECT_ID(N’Test_Table’); — In My case object id was 581577110



/* Drop that test table */

DROP TABLE [Test_Table];

Everything that we do in db is captured in T-Log and By using below command we can find the transactions that drop tables in the log:

SELECT [Transaction Id], [Begin Time], SUSER_SNAME([Transaction SID]) AS [User]
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = N’DROPOBJ’;

This command will show an output like below :






Above output tell us who dropped the tables as User (In this case it was gshri) but to know what tables got dropped, pass transaction id in the same function :

SELECT [Lock Information] FROM fn_dblog(NULL, NULL)
WHERE [Transaction Id] = ‘0000:00000343’
AND [Lock Information] LIKE ‘%SCH_M OBJECT%’;

In my case Output was :





Here 6 shows a database id and 581577110 shows an object id, which is the same that I captured above.

Hope it will help. Thanks.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: