0

I can see when a table is created, modified or dropped in the Schema Change History report. Or by reading the default trace. But I have not been able to find any record when a table is renamed. Where might this information be stored?

I did look at modify_date in sys.all_objects. It returns a row for the renamed table. My hope was to see some kind of log entry that indicated there was a name change.

1

The default trace does not capture this information. The rename operation is performed using sp_rename and the default trace captures CREATE, MODIFY, DELETE operations of objects (as in ALTER TABLE, CREATE TABLE, DROP TABLE) which doesn't occur when this procedure is run.

If you want to audit this action, you can create a trace for SQL:BatchCompleted events and filter the TextData looking for sp_rename.

As an FYI, here is the list of events captured by the default trace:

EventID  Event_Description
-------  ----------------------------------------------
18       Audit Server Starts And Stops
20       Audit Login Failed
22       ErrorLog
46       Object:Created
47       Object:Deleted
55       Hash Warning
69       Sort Warnings
79       Missing Column Statistics
80       Missing Join Predicate
81       Server Memory Change
92       Data File Auto Grow
93       Log File Auto Grow
94       Data File Auto Shrink
95       Log File Auto Shrink
102      Audit Database Scope GDR Event
103      Audit Schema Object GDR Event
104      Audit Addlogin Event
105      Audit Login GDR Event
106      Audit Login Change Property Event
108      Audit Add Login to Server Role Event
109      Audit Add DB User Event
110      Audit Add Member to DB Role Event
111      Audit Add Role Event
115      Audit Backup/Restore Event
116      Audit DBCC Event
117      Audit Change Audit Event
152      Audit Change Database Owner
153      Audit Schema Object Take Ownership Event
155      FT:Crawl Started
156      FT:Crawl Stopped
164      Object:Altered
167      Database Mirroring State Change
175      Audit Server Alter Trace Event
218      Plan Guide Unsuccessful

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.