Aug 22

Who did what to my database and when…

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

 

Mar 04

SSDT – Cut n Paste substituting CMDVARs

Another thing that slows me down with SSDT is testing stored procedure that use CMDVARS.

Consider this proc :

Prc1

If I wish to test that in SSMS, I can cut-n-paste it in then use SQLCMD mode and add in :setvar statements or simply replace the $(CmdSwitch) and $(Otherdb) with the appropriate values.  Ok for one or two values but what if there are lots ?  What if there is a simpler way  ? Well now there is:

Introducing “CmdVar Cut”

cmdvarcut

Selecting this option will place the text with the values substituted directly into your clipboard ready for pasting to SSMS.  As with Fast Deploy, the values are defined in Tools->Options->TSQL Smells->Deploy CMDVars.

The download for this is available from here http://dataidol.com/davebally/ssdt-tsql-smells-add-in/

Have fun

 

 

 

Jan 28

TSQL Smells in SSDT – The monster lives

What seems like many many moons ago, I released a powershell script that could detect many different “TSQL Smells“. Whilst it worked, I was never very happy with the interface, it was clunky and didnt feel quite ‘right’. One of my longer term aims was to do something about that and make TSQLSmells more accessible.
Static code analysis would have been mine prime choice, and that would make perfect sense, however after much blood, sweat and tears (and time wasted) i found that extending code analysis in SSDT is not supported, so i gave up disheartened.

However, poking around the other day I stumbled over a solution to my problems, and rekindled my interest, how about creating a visual studio add-in and dropping it straight into SSDT ?

So, without any further ado here it is “TSQL Smells in SSDT”, to run drop two files from here into your Visual Studio Addin directory, something like Documents\Visual Studio 2010\Addins and run SSDT. You may see an error saying it has failed to load (error number 80131515), in which case use the “Unblock” option in the file properties of the dll.

unblock

Now, load up your project and in the R-Click menu you should see “SQL Smells”.

SQLSmells

Click that and your error list will be populated with the Smells that you know and love.

smells

If that is to noisy for you, you can reduce the noise by unselecting smells in Tools->Options->TSQL Smells.

Have fun and let me know how you get on 🙂

Massive Hat Tip to Jamie Thomson for being a guinea pig for this process

If you are running VS2013, then the .addin needs a small tweak.
Please see http://bit.ly/1bRB1SN for details.