Zero to Hero with PowerShell and SQL Server at 24 Hours of Pass

On September 9th I am co-presenting “Zero to Here with SQL Server and PowerShell” for the 24 Hours of PASS. If you’ve not heard of 24 Hours of PASS, it is 24 straight hours of online presentations. This time the sessions are a preview of the SQL PASS Summit in Seattle, WA in November.

At the PASS Summit I, along with two co-workers, am presenting a full day Pre-Con entitled Zero to Hero with PowerShell and SQL Server. I’m also doing a regular session, Make SQL Server POP with PowerShell.

The session for 24 Hours of PASS will take place at 00:00 GMT on September 10th, or for those of us in the states, September the 9th, 8 PM Eastern, 7 PM Central, 6 Mountain, or 5 Pacific. The session is titled the same as the precon, Zero to Hero with PowerShell and SQL Server. Through the preceding link you can see more about the session, get to the full schedule, and most importantly register!

Be sure to check out my co-presenters too, Bradley Ball (@SqlBalls | ) and Jason Strate (@StrateSQL | )

PowerScripting Podcast

I just wanted to give a thanks to the guys at the PowerScripting Podcast for having me on tonight. As soon as it is released I’ll follow up with a link.

For those who came here from hearing me on the podcast, you can find more info on SQL Saturday at:

If you want to find out more about my sessions at the PASS Summit, you can jump to My co-presenters for the precon are Brad Ball @sqlballs and Jason Strate @stratesql.

The Pragmatic Works webinars can be found on the company website at Just follow the Free Training on the T’s to get access to the webinars. You can search by author name (Robert Cain will get you mine) or topic.

My other training videos can be found on Pluralsight,

I also have a youtube channel with a couple of videos, Check out the Column Mode Editing video for a quick editing tip on making your life easier with both PowerShell and SQL Server.

What’s New in PowerShell 3… and 4!

I did a webinar today for Pragmatic Works, What’s New in PowerShell 3… and 4! You can find the recording at:

The demo files and slides can be found at:

I had some audio issues during the early part, my apologies and bear with me as it gets better once I switched to phone.

Arcane-SQL–A PowerShell Module for Generating SQL Code


There are many PowerShell modules available for assisting the busy DBA with managing their SQL Server environment. This isn’t one of them. This module is targeted toward SQL Developers, with special functionality for data warehouse developers. A common task for BI professionals, one that is performed on almost every project, is the creation of a staging area. This might be a set of tables in the data warehouse, perhaps in their own schema, or in an entirely separate database often called an operational data store (ODS).

The staging tables are typically similar in structure to the ones in the source database. Similar, but not identical, as there are some small modifications which are commonly made to the staging tables. First, large data types such as VARCHAR(MAX) are seldom useful in data analysis and thus could be removed. Next, even the most casual user of SSIS will quickly see SSIS prefers to work with the double byte character sets (WSTR in SSIS, which maps to NVARCHAR in T-SQL) as opposed to the single byte (STR/VARCHAR) character sets. It can be helpful to convert these in the staging area.

This Module can (optionally) do all of these things and more when it is used to generate CREATE TABLE or SELECT statements. Imagine if you will a source system with thousands of tables and the need to create a staging area for it in a new data warehouse. This quickly becomes a long, boring tedious task. Now imagine being able to write a bit of PowerShell code and generate these tables in just a few minutes time.

Before diving in, it is highly suggested you download and review the example script, Arcane-SQL-Example.ps1. This demonstrates the most commonly used functions and provides patterns for their use.


While the module is full of functions, there are a few core ones that should be highlighted. Complete documentation can be found in the module itself, which has been fully documented using the native PowerShell Help system. In addition there is an example script file which demonstrates some of the most common tasks.

  • Enable-SqlAssemblies – This is the most important function, without calling it nothing else works. Be aware the SQL Server assemblies (including the SMO – SQL Management Objects – and SQL Provider) need to be on the machine where this script is run. This module has been tested on, and intended for, SQL developers with SQL Server Developer Edition installed on their workstations.
  • Join-InvokeInstance and Join-ProviderInstance – Most of the interaction done with the SQL Provider requires the server name and instance name, assembled in a path like syntax. The Invoke-SQLCommand likewise requires this formatting, however it has a little quirk. If the instance is "default" then the Invoke requires it to be omitted while the provider requires it to be present. These two functions reduce the confusion, simply pass in the server name and instance, and they will format things correctly.
  • Get-TablesCollection – When working with tables it is common to iterate over all the tables in a database. This function will generate a PowerShell array of table objects, each object being of type Microsoft.SqlServer.Management.Smo.Table. By having table objects the wide variety of properties for the table are available, such as Schema name, Table name, and Row Count.
  • Get-TableByName – Most commonly scripts will retieve an array of tables using the above Get-TablesCollection, then iterate over them in a foreach loop. There are times however when only a single table from the collection is desired. For those types the Get-TableByName can be used to retrieve a specific table object based on the name of the table. 
  • Remove-SchemasFromTableCollection and Select-SchemasInTableCollection – Get-TablesCollection will return an array of all the tables in a database. Often there is a need to only work with a subset of that table collection. These two functions will filter based on the schema and return a new array. The first, Remove-SchemasFromTableCollection, removes all tables from the array of schemas that are passed in. The second, Select-SchemasInTableCollection, will retain only those tables in the schemas passed into the function.
  • Remove-TablesFromTableCollection and Select-TablesInTableCollection – These work as filters, similar to the functions above. Instead of the schema however, they are based on table name. All tables that begin with the text passed in are either removed, or in the latter function the only ones retained.
  • Get-PrimaryKeyIndex – Returns the primary key object for the passed in table object.
  • Get-PrimaryKeyColumnNames – returns a comma delimited list of the column names in the primary key
  • Decode-IsPrimaryKeyColumn – Will determine if the passed in column name is part of the primary key index
  • Get-TruncateStatement – Will generate a SQL Truncate Table statement based on the table object passed in.
  • Get-DropTableStatement – Generates a Drop Table statement, including the check to see if the table exists, for the passed in table object.
  • Get-CreateStatement – To simply say this function generates a create table statement would do it disservice. It will take a table object and reverse engineer it, generating a create table statement. Unlike other code generators, it has a suite of parameters which allow customization of the generated statement with an eye toward the needs of a data warehouse developer. A few are:
    • DataTypeAlignColumn – Set the column number to line up the data type declarations on. Passing in a value of 1, will suppress alignment and simply place the data type after the column name. The default is column 50.
    • OverrideSchema – It is common place staging tables in the data warehouse in their own schema, often named ‘Staging’ or ‘ETL’. Passing in a value here will include the new schema name in the create table declaration. If the table object passed in had a schema other than dbo, it is placed in front of the table name with an underscore. If it was dbo, the source schema is simply omitted.
    • PrependToTableName and AppendToTableName – Allows extra text to be placed before or after the table name. For example, it is common to create tables with _Delete, _Update, and _Insert in the staging area. This provides a simple way to do that.
    • AdditionalColumns – When creating tables in a data warehousing environment, there are often extra columns to hold metadata about the ETL process. A user of this function can create an array of additional columns using the Add-ColumnDefinition function and have them added to the create table statement.
    • Scrub – This is a very powerful switch. When added it will perform a cleanup to make the output suitible for data warehousing. Columns with large data types such as VARCHAR(MAX) are removed. All single byte character sets in the source are converted to double byte sets.
    • SuppressIdentity – Source systems will sometimes use the IDENTITY clause in the primary key column. Using this switch will suppress that identity clause from being generated in the new create table statement.
    • SuppressNotNull – Often staging tables will not be concerned with null versus not null values. Using this switch will create all columns as nullible, regardless of their setting in the source.
    • IncludeDropTable – Adding this switch will include a ‘if exists drop table’ style clause prior to the create table statement.
    • PrimaryKeysOnly – Will generate a create table statement that only has the primary keys found in the source system.

Finally, if a column in the source table object has a custom data type, the script will reverse engineer the data type back to its basic SQL data type.

  • Get-SelectStatement – Like its sister function Get-CreateStatement, under the covers this function provides a lot of power and flexibility to the statement it creates. Additional columns can be added, columns can be specified to order the output by, table aliases can be used, and most powerful of all is the ability to generate a HASHBYTES column, including the ability to remove specified columns from the hash byte calculation. Here are some of its parameters:
    • AsColumn – The routine will line up the AS <column alias> at the column number passed in here. The default is 50. To not use aligning, set this to 1.
    • PrependToColumnName – Text to include before each column name.
    • AppendToColumnName – Text to place after each column name.
    • AdditionalColumns – A collection of additional columns to be added to the SELECT statement. Useful for adding metadata columns. All items in the AdditionalColumns collection should be generated using the Add-OutputColumn function.
    • OrderByColumns – A list of columns to add to the ORDER BY clause. All items in the OrderByColumns collection should be generated using the Add-OutputColumn function.
    • TableAlias – Allows user to specify an alias to use for the table. The alias is then put in front of each column name.
    • HashBytes – If included the select statement will include a HASHBYTES function with all columns except the primary keys and any columns included in the OmitFromHashBytes collection. The name passed in this parameter will be used for the name of the HashBytes column.
    • OmitFromHashBytes – A collection of column names that should be excluded from the HashBytes calculated column. Useful for excluding metadata columns. All items in the OmitFromHashBytes collection should be generated using the Add-OutputColumn function.
    • Scrub – When included this will remove certain data columns from the output, such as BINARY, NVARCHAR(MAX), XML, and other large types not normally used in data warehouses. Additionally VARCHAR/CHAR are converted to NVARCHAR/NCHAR, and DATETIME converted to DATETIME2(4).
    • Flatten – When included will return the SELECT statement as one long string, without any Carriage Return / Line Feed characters. Additionally, any additional spacing (such as indicated with the AsColumn) is eliminated.
    • IncludeOrderByPK – When included the Primary Keys in the table object are included in the order by clause. If any columns are passed in the OrderByColumns parameter, the Primary Keys occur first in the Order By clause, followed by any columns in the OrderByColumns parameter.
    • IncludeNoLock – When included, a WITH NOLOCK clause is added to the SELECT statement.


Those PowerShell experts who review the code may note that in many places code does not follow the most "powershelly" way of doing things. In some places rather than using pipelining it was instead decided to use a foreach loop, for example. The intended audience for this module are T-SQL developers who may not be as comfortable in PowerShell as they are T-SQL. Thus using code that more closely aligned with T-SQL patterns would make it more useful and modifiable by SQL developers.

When development first started attempts were made to use advanced functions, using the pipeline for input and output. At some point however this didn’t make sense for a majority of the functions. Time constraints further impinged this effort. Some future revision may attempt to migrate selected functions back to an advanced design, but for now they will have to stand as is.

Development Environment

This module is intended to be used on a developer workstation, not on a server, and especially not on a production server. As such deployment has been made simple, just copy the Arcane-SQL folder to the developers PowerShell module library. On a standard Windows 7 machine this would be C:\Users\<<usernamehere>>Documents\WindowsPowerShell\Modules. If the Windows PowerShell folder and modules subfolder do not exist, they will need to be created first.

To keep things simple, no attempt was made to sign the script. If this is an issue the developer using this module can self sign it on their PC. Check the execution policy on the workstation where the module is installed to ensure sufficient rights to run the module.

This module was developed on machines with both SQL Server (Developer Edition) 2008R2 and 2012 installed (some machines with both) and worked without issue. One machine it was tested on had 3 versions of SQL Server, 2008R2, 2012, and 2014. On that one machine there were some errors with some of the functions passing in the SMO table objects. Those are still being investigated.

SQL Security was assumed to be handled using built in Windows Credentials. Thus the logged in user would need to have rights based on Windows credentials to the SQL Server they are targeting. 

The machine being developed on was using PowerShell v4, however v3 should work as well.

This module was developed using SAPIEN PowerShell Studio 2014. To make life easy for other developers the PowerShell Studio files (Arcane-SQL.psproj,, and Arcane-SQL.psprojs) were included in the code. If you are using a different editor, such as the PowerShell ISE, simply discard these files.


To put it succinctly, there is none. No guarantee is made for the code in this module, users of this module assume all risks. While I am happy to receive bug reports, I make no promises or guarantees if, or when, they will be fixed.


No, not the money kind, code contributions. If anyone wishes to extend the functionality of this module I am happy to collaborate as long as the coding standards demonstrated in this module are adhered to, and the contributions are relevant to the goals of this module. Be aware though this is not a money making effort, so expect no monetary reimbursement for any contributions.


You can download the module and its example at:

Arcane Fun Fridays–Going Retro with PowerShell

In terms of the tech world I’m an old geezer. I actually remember the days of monochrome screens, both the amber and green kind. I even have my old Commodore 64 with its light blue font on dark blue background. So I was feeling a bit nostalgic, and decided to create some shortcuts to PowerShell windows with a retro look.

For pre Windows 8, in the start menu navigate to Start then go to your Accessories and/or Administrative tools (varies with which version of Windows you have). Find the icon for Windows PowerShell (not the ISE, just the regular window). (I figure if you are using PowerShell, you probably know where it is without me having to tell you.)

Right click on it, pick “Send To”, then “Desktop (create shortcut)”.

If you are on Windows 8, the simplest thing is to go to your start menu and locate Windows PowerShell. Pin it to your task bar. Now, desktop taskbar shift + right click (the shift is very important when you right click) and do the same Send To / Desktop I mention above.

OK, you now have a short cut, let’s start our retroization. First, give it a decent name. For my example I’ll pick “PS Amber”. Next, right click on it and pick Properties. In the dialog click on the Colors tab. Click on Screen Text, and set the Red / Green / Blue to 255, 185, 0 respectively.


Now, this is the part that will bring back the cool “retro” effect. If you are an old geezer like me, you’ll recall the amber monitors also had a big of an orangey background, they weren’t entirely black. Go to the Screen Background, and set its Red value to 32, then use 0 for Green and Blue. That will give it just the hint of orangeness to make it authentic. Of course based on your monitor calibration you may need to tweak it up or down just a tad, adjust until you are happy.

For the Pop-up text, I used the same settings as the Screen Background, and likewise for the Pop-up Background I used the same colors as the Screen Text. And here’s what I wound up with (click on it for a bigger image):


Following the same steps above, I created a second short cut called PS Green. I set its Screen Text to an RGB of 0, 255, 0, and the Screen Background to 0, 32, 0. Like with the previous section, I used the reverse colors for the pop ups, 0,255,0 for the background and 0, 32, 0 for the popup text.


And finally, the setting that made me wax eloquently over my beloved old Commodore 64:

Screen Text and Popup Background: 44 / 136 / 255

Screen Background and Popup Text: 0 / 0 / 34


(If you are too young to remember the Commodore 64, you can read more about it on Wikipedia or see it in action, there’s a slew of videos about it on youtube

Setting up different shortcuts to different color themes can not only make you feel nostalgic, but it can also be quite beneficial when you have multiple PowerShell windows open at the same time. The drastically different colors can make it easy to know which window you have doing what task.

Have fun with it, adjust the colors until they look just right on your own monitor, then create your own crazy color combos. If the are really good post them in the comments area. If I get enough I’ll create a follow up post, giving appropriate credit for each submission.

SAPIEN PowerShell Studio 2014–Customization Redux

Earlier in the week (post) I started this series on SAPIEN PowerShell Studio 2014 discussing customization. How appropriate to wrap up returning to that same topic.

The original version I downloaded was 4.1.47. Late last night I found out a new version has already been released, 4.1.49. I downloaded it and to my pleasure found two more options for color themes, Visual Studio 2013 Dark and Visual Studio 2013 Light.


If you read my earlier post you’ll already know what I picked, the new VS dark theme is much darker and richer than the Office 2013 black theme. Take a look:


Note that the colors of the editor are not set with this selection, only the backgrounds for things like the menus, title bar, borders, etc. If you want the nifty dark settings in the editor you’ll still need to set them yourself, or use the XML data I listed at the bottom of the already mentioned first post in the series.

You may be wondering how I found out about the update with these new features. Well, SAPIEN has a particularly nice set of forums, located at As you might expect there are forums for all of their products, but you will also find forums for the various scripting languages. In here you can ask generic questions not directly related to any of their products.

After my initial download I had placed a post asking some questions, specifically this post:

Within an hour a support person named David had answered my questions, one of which as you’ll see was on the color themes.

Now, what particularly impressed me was four days later David remembered my forum post, and placed a follow up post letting me know about the update with the new dark color theme. Now that is some outstanding customer service, big kudos to David (whoever you are) for the follow up.

SAPIEN PowerShell Studio 2014–Tools

As I’ve been working with the SAPIEN PowerShell Studio (website), there are a few things in the Tools menu I have found useful. I wanted to call these out, as some may overlook what could be a set of very beneficial items.

If you navigate to the tools menu, over to the left you’ll see these tools:


Find in Files is pretty obvious, but don’t over look it. How many times have you been working on something and thought “OK, I wrote something like this once, but just which file was it in?” This will allow you to search for a string you enter. You can specify the folder, and can add a list of file types to look through. By default it will limit the search to PowerShell oriented files, but you could change this easily if, for example, you wanted to search through a CSV file. Especially nice is the ability to use regular expressions or wild cards.


Compare Files does just what it says. You select two files, and it will compare them and produce a report of the differences.

Custom Tool is interesting, essentially it becomes a menu you can customize and use to launch executables, parameterized as you want it. Rather than repeating what is already published, I’ll point you to a blog post on the SAPIEN website which explains how to customize this for your needs.

Check Syntax is great, it will quickly look through the current script and identify any syntax errors. I’ve gotten to the point where I always use this after making major edits to a script. Much nicer to find out this way rather than after you start running the script.

Verify Script is similar, but instead of syntax it checks to see if all the required “pieces parts” are present. For example, it looks at any Import-Module statements and validates that those modules are indeed available, and that the functions you reference are there.

Sign Script I haven’t needed to use yet, but if you are in an environment where this is required than this will be a great little shortcut for you.

Restore Points are the last item on the list. When you create a restore point, you can then go make changes to your code, then if you don’t like them you can Rewind to the previous restore point, or use the Restore button to revert back to the point at which you created the original restore point. Once you are happy with your code you can then Delete the restore point.

This is ideal for those situations where you think “hmm, I wonder if this would work…” and want to try out something. But, if it doesn’t you want to be able to roll back to the point prior to your editing. Previously you would have needed to make a copy of the PS1 (or whatever you are editing) file, make changes, then either copy the file back or copy parts of it back into your code. Yuck. Restore Points make this much easier. Even better, the persist between sessions. You can close the entire PowerShell Studio, and when you return and reopen the file those restore points are still active.

I am amazed how little many developers know about the tools at their disposal. Often people look no further than the main tab and never explore the rest of their environment.

Even though individually these tools may seem like small things, together they provide quite a toolbox to solve a lot of common, everyday issues developers face. I hope it encourages you to more fully explore the SAPIEN PowerShell Studio 2014.


Get every new post delivered to your Inbox.

Join 110 other followers