PowerShell vs. SQL Server Management Studio

When I (along with many other people) had a lot of trouble trying to install SQL Server Management Studio in an attempt to switch from the SQL Server 2008 R2 evaluation to the free Express version, I became quite frustrated and began looking at free alternatives to SSMS, including PowerShell.

When I wrote about this, one reader replied that my post was a total disappointment because PowerShell, not having a graphical user interface (GUI), was not a suitable candidate to replace SSMS. I found this comment puzzling, as I included a screenshot of the PowerShell Integrated Scripting Environment (ISE), which looks pretty darned GUIish to me.

TechNet seems to agree with me, as it says, "In Windows PowerShell ISE, you can run commands and write, test, and debug scripts in a single Windows-based graphic user interface [emphasis mine] with multiline editing, tab completion, syntax coloring, selective execution, context-sensitive help, and support for right-to-left languages."

But hey, I'll give the reader the benefit of the doubt. Maybe "graphic user interface" is different from "graphical user interface" and PowerShell doesn't qualify as an official GUI because you often have to enter scripting commands, or something.

Anyway, I pointed the reader to PowerGUI, which I hope to explore further.

In that same post, I commented that SSMS has some things that PowerShell doesn't, such as an object explorer. PowerShell expert Chad Miller commented (on VisualStudioMagazine.com another site hosting my blog) that PowerShell is quite extensible and allows you to build your own object explorer and directed me to the SQL ISE, part of the SQL Server PowerShell Extensions CodePlex project (SQLPSX).

So I checked it out and learned that "SQLPSX consists of 13 modules with 163 advanced functions, 2 cmdlets and 7 scripts for working with ADO.NET, SMO, Agent, RMO, SSIS, SQL script files, PBM, Oracle and MySQL and using Powershell ISE as a SQL and Oracle query tool. In addition, optional backend databases and SQL Server Reporting Services 2008 reports are provided with SQLServer and PBM modules."

Impressive, but I wondered about the DIY object explorer. I downloaded and installed all the requisite files and modules and an "Add-ons" item was added to the top menu. Clicking on that brings up a "SQLIse" menu item with a dozen submenus, one of which is "Object Browser." That indeed does bring up something similar to the SSMS object browser, as a new window, as shown in Fig. 1, exploring the Northwind database:

[Click on image for larger view.]

Figure 1. Exploring the Northwind database with the SQLIse object explorer.

It doesn't have the exact same capabilities as its SSMS counterpart (such as right-click functionality to run queries, and so on), but it certainly lets you explore the database tables.

Being a PowerShell noob, I wanted to explore more database-related functionality. I asked PowerShell MVP Doug Finke what he thought about the usefulness of PowerShell for database developers. "PowerShell is useful for devs in many ways," he replied. As a simple example, he pointed to the Invoke-SqlCommand, from Lee Holmes of the PowerShell team, and author of Windows PowerShell Cookbook.

Finke provided the following command example:

Invoke-SqlCommand -Sql "Select * from pubs" | Export-Csv  -NoTypeInformation c:\pubs.csv; 

  Invoke-Item c:\pubs.csv

"This extracts the data, puts it in a comma-separated-format file and then uses Invoke-Item to launch Excel on that file," Finke said. I tried his example, and sure enough, the result is shown in Fig 2.

[Click on image for larger view.]

Figure 2. Using PowerShell to access the Pubs database and export its contents into a .csv file.

Finke continued: "[Here] is a post I did doing more tricks with Excel and pivot tables. While the data in the post comes from a csv, it could come from SQL Server or any other data source. This is a key benefit to the object pipeline."

Finke also listed more benefits to database developers offered by PowerShell:

1. Testing Web services using PowerShell's New-WebServiceProxy cmdlet.
2. Microsoft Nuget has a PowerShell console that resides inside Visual Studio 2010 and makes using it more productive.
3. A developer can load and test .NET DLLs at the command line or in scripts.
4. PowerShell can be used to automate common, everyday developer tasks including source control builds.

I was curious about item No. 3 in that list, so I gave it a try. Sure enough, using the .NET Math DLL is as simple as calling it in brackets and then using :: to use one of its static methods, in this case the square root function. I assigned the value produced by the function to $v and called for the square root of 49, like this:

PS  C:\Users\Ramel\Desktop> $v = [MATH]::Sqrt(49)

Then, to see the value returned, you can just enter the variable at the prompt: $t.

That produces the result: 7

You can also make and access your own DLLs. For example, in Visual Studio, I created a Class Library project, connected to the Pubs database and built a Pubs DLL, which I accessed with this command:

PS  C:\Users\Ramel\Desktop> [System.Reflection.Assembly]::LoadFile("C:\pathtodll\Pubs.dll") 

I then created a new object -- a Pubs DataContext -- and assigned to a variable called $ctx. Just by typing in the variable and hitting enter, you could see the structure of the object:

authors                 : {White, Green, Carson, O'Leary...}

  titles                  : {BU1032, BU1111, BU2075, BU7832...}

  ...

Once you see the structure, you can call up all author records, for example, like this:

PS C:\Users\Ramel\Desktop> $ctx.authors

  au_id        :  172-32-1176

  au_lname     : White

  au_fname     : Johnson

  phone        : 408  496-7223

  address      : 10932 Bigge Rd.

  city         : Menlo Park

  state        : CA

  zip          : 94025

  contract     : True

  titleauthors : {172-32-1176}

  ...

You can then run SQL queries against the DataContext in a number of different ways, including the following, which finds authors who live in Utah:

PS C:\Users\Ramel\Desktop> $ctx.authors | where-object  {$_.state -eq "UT" }

  au_id        :  899-46-2035

  au_lname     : Ringer

  au_fname     : Anne

  phone        : 801  826-0752

  address      : 67  Seventh Av.

  city         : Salt Lake City

  state        : UT

  zip          : 84152

  contract     : True

  titleauthors : {899-46-2035, 899-46-2035}

  ...

So even though I've just scratched the surface of what you can do in PowerShell, I can see that while it doesn't duplicate the complete functionality of SSMS, it can duplicate a lot of it. And the extensibility, .NET compatibility and many other features make it a worthwhile consideration.

How do you use PowerShell in your database development? Comment below or e-mail [email protected].

Posted by David Ramel on 06/24/2011