One of the most popular questions on forums / SO etc is, “How can i find out who dropped a table, truncated a table, dropped a procedure….” etc. Im sure we have all been there, something changes ( maybe schema or data ) and we have no way of telling who did it and when.
SQL auditing can get you there, and i’m not suggesting you shouldn’t use that, but what if that is not setup for monitoring the objects you are interested in ?
If you look back through my posts, I have been quite interested in ScriptDom and TSQL parsing, so if you have a trace of all the actions that have taken place over a time period, you can parse the statements to find the activity.
Drawing inspiration ( cough, stealing ) from Ed Elliot’s dacpac explorer, I have created a git repo for a simple parser that is created using T4 templates. T4 templates are an ideal use here as the full language domain can be exploded out automatically and then you as a developer can add in your code to cherry pick the parts you are interested in.
At this time the project has hooks to report on SchemaObjectName objects , so any time any object is referenced be that in a CREATE, SELECT , DROP, MERGE … will fall into the function OnSchemaObjectName and OnDropTableStatement that will be hit when DROP TABLE is used.
This is not intended to be a full end to end solution, not least as the SQL to be parsed could be coming from and number of sources, but if you have the skills to run it as is , you probably have enough to tailor it for your requirements.
As ever, let me know what you think and any comments gratefully received.
The github repo is : https://github.com/davebally/TSQLParse
