SQL Server Management Studio - history of commands run

Question

I have previously altered a table, added a column and modify a stored procedure.

I do not remember exactly which table and which stored procedure, is there some log in sql management studio about what changes were done in a particular DB?

I have tried what is described here How to see query history in SQL Server Management Studio but i have not found the changes made to the DB

Answer 1

There's a few ways that you can get this information. Firstly you could try the standard reports --> Schema Changes History.

The information for this comes from:

SELECT cat.name AS Category
, b.name AS EventCaptured
, c.name AS ColumnCaptured
FROM fn_trace_geteventinfo(1) AS a
INNER JOIN sys.trace_events AS b
ON a.eventid = b.trace_event_id
INNER JOIN sys.trace_columns AS c
ON a.columnid = c.trace_column_id
INNER JOIN sys.trace_categories AS cat
ON b.category_id = cat.category_id
ORDER BY Category, EventCaptured, ColumnCaptured

Alternatively, query sys.traces to find the location of the default trace and feed this into fn_trace_gettable as per below.

SELECT *
FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default)

Answer 2

You can use Security\Server Audit Specification and enable DATABASE_OBJECT_CHANGE_GROUP audit on your database.

Use following reference in order to use SQL Server server audit.

1. CREATE SERVER AUDIT SPECIFICATION (Transact-SQL)
2. Create a Server Audit and Server Audit Specification

Answer 3

You can use the modify_date column in sys.objects table

SELECT *
FROM SYS.OBJECTS
WHERE Modify_Date BETWEEN <date_of_modification> AND <date_of_modification> + 1

and then you can try to narrow it down.

You can be even more specific and run the query for just tables and stored procedures.

SELECT *
FROM SYS.objects
WHERE TYPE IN ('IT', 'S', 'U', 'P', 'PC', 'X')
    AND modify_date BETWEEN '10-Jun-2014' AND '11-Jun-2014'

Answer 4

I develop SSMSBoost add-in for SSMS and we have "Executed Query history" there. All actions are logged with timestamp, connection information and execution result. This will certainly only work, if you do all changes from SSMS with SSMSBoost installed. If someone will perform changes from other machine you will not see them, until he uses SSMSBoost as well and you share execution history.