How to capture truncate statement information in SQL Server 2012

Question

I would like to capture the truncate statements information along with the user/Login information for all database in my production server.

Example:

Use mydb
go 
truncate table deleteme_table

I would like to capture the information into the table like the below

Table           Operation   Database    Login              Time
deleteme_table  Truncate    mydb        sandeep.pulikonda  17-12-2014 17:50:00

If the above scenario is not possible please suggest possible ways to capture it

I am using SQL Server 2012 Standard version. So granular level audit are not supported for that version.

Answer 1

you can use the SQL Server Audit functionality and add an audit for those queries.

this article explains in detail how to obtain this.

Another good way of profiling your SQL Server is using SQL Profiler. Here is a SO question similar to yours and an answer describing how to use SQL Profiler to achieve the results.

SQL Server Profiler - How to filter trace to only display TSQL containing a DELETE statement?