VSTS for Database Professionals: Power Tools v1.0 released

Gert Drapers has announced the release of the first edition of Power Tools for VSTS DBPro.  There is a prior post that discusses each of the tool’s features in greater detail.

    • Dependency Viewer
    • Refactoring
      • Move Schema
      • Expand Wildcard
      • Fully Quality Name
      • Refactor in to strongly typed DataSet definitions
      • Refactor Command Generator
    • Data Generation
      • Sequential Data Bound Generator
      • Editors for the Data Bound Generator, Sequential Data Bound Generator and RegEx String Generator to make configuration easier
      • The RegEx editor also tries to interpret your CHECK CONSTRAINTs and create a matching RegEx expression that you can use to generate data values that match the constraint definition
      • The RegEx editor can also be used for interactively defining and testing RegEx expressions and evaluate the output visually, which makes it a lot easier to create the right RegEx expression for your value domain.
    • MSBuild Tasks
      • SqlSchemaCompareTask; allows you to compare schemas between two database from the command line using MSBuild.
      • SqlDataCompareTask; allows you to compare the content of tables within two databases from the command line using MSBuild.
    • T-SQL Static Code Analysis
    •  Miscellaneous tools
      • SQL script pre-processor command-line utility, which will expand all SQLCMD includes and variable definitions (sqlspp.exe)
    • Schema Manager API                     

                                                                                                                     – Gert Drapers

I’ve pared down Gert’s list to show highlight a couple of items that I really look forward to using. 

  1. The RegEx editor’s ability to review a CHECK CONSTRAINT and build you a RegEx to generate appropriate data values is a godsend for those of us that are “regular expression challenged.”
  2. The SqlSchemaCompareTask would be extremely useful during a continuous integration build to check the schema of a “reference database” to one that was deployed during the build as part of the Build Verification Tests (BVTs).  Differences could be flagged and noted in the build log which could be indicative of a schema change that was not checked in.
  3. The SqlDataCompareTask is a great tool for use during automated acceptance testing.  After checking the schema of the deployed database against a reference database, you can check the deployed data against the test-reference data to ensure consistency between test runs.
  4. T-SQL Static Code Analysis is a hidden gem in this list.  As Gert put it, “[…] it build on top of our ability that we fully understand all object definitions and relationships, which allows us to perform cross cutting analysis over your schema. For example we can detect that a column is defined as an INT, a variable of a stored procedure as REAL and that you are assigning the column value to the variable, therefore incurring the cost of an implicit conversion.”  For developers that aren’t full-time DBAs, this is a way to keep us from shooting our selves in the performance-foot as well as help us learn better techniques.  Like FxCop, this analysis engine is configurable and you can set the rule violation to be either Warnings or Errors.