• Practical PowerShell for SQL Server Developers and DBAs – Part 1

• Practical PowerShell for SQL Server Developers and DBAs – Part 2

Contents

• Navigating in SQL Server Space
  • Data Stores and Drives
  • Nodes in SQL Server Space
  • Cmdlets Implemented by the SQL Server Provider
  • Mapped Drives
• Working in SQL Server Space
  • Understanding and Displaying Nodes
  • Finding Meta-Information: Row Counts
  • Finding Meta-Information: Scripting DB Objects
• Conclusion: The Spectrum of PowerShell / SQL Server Entities

If you have not done so already, please review Part 1 to get the most out of this article. Also see the accompanying wallchart (link starts PDF download) that distills the key details out of both parts into a one-page reference.

Navigating in SQL Server Space

Data Stores and Drives

If all you could do was execute queries, PowerShell would be a weak cousin indeed to something like SQL Server Management Studio. But PowerShell allows you to navigate and explore “SQL Server space” from the command line in a similar way that you would use SSMS’s object explorer and context menus. Before tackling this capability, however, you need to have an appreciation for PowerShell data stores. First, realize that these data stores are quite distinct from SQL Server databases. Each PowerShell data store is managed by a PowerShell provider (see the PowerShell help page about_providers for more details). If you run the Get-PSProvider cmdlet you will see a list similar to this:

Start with the most familiar provider, the FileSystem provider. On my system it manages four drives, the ubiquitous C: drive along with the D:, E:, and F: drives. As you are likely well aware, you can set your current location to any of these drives and then navigate around the drive to access various programs and files. Unlike the old Windows command shell, you do both of these commands (going to a drive or going to a folder) exactly the same in PowerShell:

Once you go to a particular location, you can list the contents using the Get-ChildItem cmdlet (aliases: dir, gci, ls). Or, as you might expect, you could specify a path to Get-ChildItem without changing your current location (e.g. ls C:\usr\tmp).

PowerShell has abstracted this concept in a novel and powerful way to other data stores. Notice, for example, that there is a Registry provider, which by default provides two “drives”, the HKLM: and HKCU: drives. Just as you would expect, you can set your location to either of these drives, then navigate the registry tree with the same Set-Location cmdlet. Filesystems and registry hives have a hierarchical structure; other drives managed by the providers shown do not. For example, you can navigate to the environment data store (cd env:) but there is nowhere else to go from there. What you can do, however, is list the contents of the drive (ls env:).

Now take a look at the SqlServer provider, managing two drives on my system. The first (SQLSERVER:) is the default you will find whenever you have the SQL Server provider installed. The second is a custom drive that I have instantiated; more on that later. This SQL Server provider is courtesy of the familiar sqlps module, discussed earlier. When you import the module not only does it load SQL Server cmdlets but it also loads the SQL Server provider and auto-mounts the SQLSERVER: drive.

Nodes in SQL Server Space

The SQLSERVER: drive provides a number of root-level items that you can explore. The main one of interest (discussed below) is \SQL which contains all your database objects; others contain policy, server, integration service, and analysis service objects-see the root-level hierarchy detailed on the SQL Server PowerShell provider page on MSDN for more details on these other object types.

The table below specifies the paths to some of the locations you will most commonly use.

When navigating in file system space, you may either change your current location to a particular node (e.g. Set-Location C:\a\b\c) or from your current location you may reference a particular node (e.g. Get-ChildItem C:\a\b\c). In SQL Server space you have the same options. You can, for instance, Set-Location SQLSERVER:\SQL\localhost or you could Get-ChildItem SQLSERVER:\SQL\server509\DEFAULT\Databases\sandbox\Tables. As you might expect, the machine in a path may either be a specific server name or you can use localhost to refer to your current machine.

All the nodes under the \SQL node are deterministic; each time you display the contents of \SQL\localhost\SQLEXPRESS\ you get a list of all your databases. But the top-level \SQL node itself is unique. This displays a list of the machines you have touched in the current session. Assuming your machine name is gandalf, in a fresh PowerShell session Get-ChildItem \SQL will return two entries, gandalf and localhost. Each time you connect to a different server by naming it in a path (e.g. Get-ChildItem SQLSERVER:\SQL\server509\DEFAULT) you add that machine to the list of known servers so henceforth it will also appear when you list the contents of the top-level \SQL node. Thus, after the first such reference, Get-ChildItem SQLSERVER:\SQL returns two entries for your local machine (the actual name and localhost) plus any machines you have referenced, in this case server509:

This example also illustrates a PowerShell feature that is not obvious (until you see it the first time): the output of a cmdlet varies based on the provider. In contrast to the output of the SQL Server provider on the \SQL node, for the file system provider Get-ChildItem returns output like this, showing properties you would expect for files and folders:

Furthermore, Get-ChildItem could even return different output within a single provider based on context. The table below lists the same common SQL Server paths shown earlier, this time listing their default output from Get-ChildItem. Most of the time Get-ChildItem returns a list of objects with the properties shown (in the two highlighted cases, however, it is not even returning objects, just a list of strings). In the case of file system space, every node in the tree is essentially equivalent to every other node with respect to its objects’ types, either files or directories. In SQL Server space, however, almost every node is dealing with different objects; hence you should expect a description of those objects to vary.

The table lists the default properties displayed for each type of object when you use Get-ChildItem but the complete set is often much more vast. After completing discussion of navigation in SQL Server space with mapped drives, the next major section illustrates what additional information you can glean from Get-ChildItem.

Cmdlets Implemented by the SQL Server Provider

You have already seen examples of PowerShell cmdlets that are implicitly supported by the SQL Server provider: Get-ChildItem and Set-Location. There are several other important cmdlets supported, providing important functionality:

Mapped Drives

Windows has had the concept of mapped drives for many generations, where you could provide a drive-letter alias to an arbitrary path, referencing your local file system or even a remote file system. This could be done from the Windows GUI, of course, but from the command-line you would use this command:

PowerShell provides an analogous command, but it allows you to create a mapped drive (or drive-letter alias) with any provider, not just the file system provider. The syntax for the SQL Server provider is:

Unlike DOS drives, the drive name may be multiple characters (like the SQLSERVER: drive, for instance). And like many PowerShell commands, there are built-in aliases for the New-PSDrive cmdlet (ndr or mount). You have seen that the path to any objects within a database is rather lengthy. So use a command like this to make a shortcut:

That command allows you to reference…

instead of…

Earlier you saw the output of the Get-PSProvider cmdlet listing current drives by provider. You can get a more detailed view of the drives themselves with the Get-PSDrives cmdlet:

I have highlighted the two drives on my system supported by the SQL Server provider, the default SQLSERVER: drive and my custom DB: drive. This cmdlet shows you what the drive is aliased to along with your current node location on that drive (i.e. the last location you navigated to with Set-Location).

Working in SQL Server Space

Understanding and Displaying Nodes

In the previous section you learned that in SQL Server space, unlike file system space, virtually every level of the hierarchy returns a different type of object. One table showed you the default properties of each object. But what object are we talking about? The table below enumerates the object type for each level. With the object type in hand, you can find its MSDN reference page to see documentation for all its properties.

The object types were determined from this simple PowerShell sequence:

For example:

Each object may specify a default set of properties but this is often not the complete set of properties. The object type is again key to determining this default set of properties-and the default format (list or table)-for an object. First, PowerShell looks for a formatting file, which specifies both default properties and default formatting on individual .NET types. The main PowerShell system directory lists standard types but some modules, including sqlps, supplement this with additional formatting files. $HOME\Documents\WindowsPowerShell\Modules\sqlps\SQLProvider.Format.ps1xml defines the various database objects discussed here. If not specified in a formatting file, the default properties of an object will be gleaned from the DefaultDisplayPropertySet property if present. If that property is not defined, then there is no default set so all properties are displayed when an object is output. This selection process is summarized in the table.

After the default property set is determined, the final determination of how to display objects is a choice between Format-List and Format-Table. You can always direct output to one of these specifically as the tail of your pipeline to mandate that format. Here’s an example with Get-ChildItem to contrast these choices. This example uses the -Force parameter with a single wildcard argument (*), which forces display of all properties rather than just the default display properties. There are actually more columns in the output below; I have truncated the list to fit the page.

The truncation within each column, however, is not my doing! PowerShell does that in its attempt to squeeze as much into your current window width as possible because Format-Table puts an entire record on one line. By doing so, sometimes the data in a column is not terribly useful (the first and second columns, for example). In such cases where squeezing onto a line turns out to be impractical use Format-List, which displays one property per line, giving plenty of room to see the property’s value. Here are the first couple objects above now given ample room:

If you do not direct your pipeline to one of the Format-verb cmdlets, PowerShell again looks to the same formatting files for direction. If the type is specified, it uses the specified format. If not found, then the final determination to use Format-List or Format-Table boils down to the number of properties to display: if more than four it uses Format-List, with four or less it uses Format-Table. Here’s a summary of the steps:

Finding Meta-Information: Row Counts

The Invoke-SqlCmd cmdlet described earlier is the workhorse to examine data in your database. But as either a developer or as a DBA, you often need to examine information about your data, rather than the data itself. One common metric is how many records are in each table. There are many ways to determine this, as a quick web search will reveal. StackOverflow provides quite a few of them in answer to this one question: how to fetch the row count for all the tables in a SQL Server database. One of the most straightforward and concise, posted by Adrian Banks, is this:

This answer actually had the most votes at the time I wrote this but there are a number of reasons that it is less than optimal:

1. Performance: doing a SELECT COUNT(*) on huge tables is a tremendous performance hit; not only could it mean you have a long time to wait for an answer, you could potentially affect many other users by locking up tables for significant periods.
2. Stability: sp_MSForEachTable is an undocumented stored procedure, so you should not rely on it.
3. Resource use: it uses a temporary table; not a big thing, but a solution that does not use one would be better.

In contrast, one of the best in terms of performance and resources, posted by Keng, is this:

I submit, though, that this variation requires quite a bit of domain knowledge to craft from scratch and deriving it is far from obvious for many of us.

Now consider the same question in the context of PowerShell. From the above discussion you have seen that PowerShell makes a wealth of meta-information available on database objects with properties. In the current scenario, you know what you want (record counts) but do not know where to find it. It is not a huge leap to infer that a count of records for a table might be a property of tables. But what property? Use the Get-Member cmdlet to reveal all the properties for tables:

That yields a lengthy list of table properties. You could scan down the list and would likely notice one called RowCount. Or you could work smarter and ask for just the properties including the word count. The command sequence below gets a table object from Get-ChildItem, lists its members with Get-Member, then filters that list with Where-Object. For the purpose of formatting for this article I have used aliases for each cmdlet in the above sequence: gci for Get-ChildItem, gm for Get-Member, and ? for Where-Object. (Aliases are quite useful when you are typing commands interactively as well!)

This command reveals just what you need: a RowCount property for a table. With this in hand, it is trivial to list the number of records for each table. Get the table objects with Get-ChildItem, select the table name and the table row count with Select-Object, and pipe the results through Format-Table to provide a cleaner output:

That lists the row counts for every table; it is almost as simple to select just a few tables. In T-SQL you would just add a WHERE clause at a strategic point in the query. In PowerShell, you add another element to the pipeline, the same Where-Object used just above to filter the property list. This command assumes there are a number of tables containing the word Big in the name:

Similarly, you could select based on schema with something like this:

Finding Meta-Information: Scripting DB Objects

Another task that you might want to tackle is to programmatically generate scripts to create your database. SQL Server Management Studio allows you to do this for any single database object with a context menu action, of course, whether it is an individual table or the database container itself. But doing this comprehensively for all your tables or, better still, for everything in your database, is non-trivial.

First consider the question of generating a script for one or more tables in your database. In fact, Stack Overflow presents this very question: How do I generate a CREATE TABLE statement for a given table? The answers posted there suggest a number of variations, all of which are fairly complex. In contrast, here is the complete code to generate table creation scripts for all tables in your database (of course, substitute the appropriate path in SQL Server space to your database):

That is, you need merely invoke the Script() method on each Table object successively. That sequence displays output on the console. To send it to a file, add on one of several file output commands, as in:

If you are a traditionalist who prefers your DDL in separate batches, use this to add the GO statement after each CREATE TABLE statement (you will find the line breaks are in all the right places):

Here is a summary of these and other variations. I again use aliases here for brevity (gci for Get-ChildItem,? for Where-Object, and % for ForEach-Object):

As you can appreciate, a very little PowerShell can accomplish quite a lot. But wait! Incredible as it may seem, this is actually the “low-level” way to script your database objects. At a higher level, you do not even need to worry about the “GO” statements, the associated objects, or even the looping. The brief script below, as you will observe, is mostly setting the various options on the Scripter object, plus a few introductory lines defining a couple variables. Beyond the bookkeeping all that remains is two lines of code, highlighted below:

The first line calls the Script() method on the database itself to generate the CREATE DATABASE code. The second line calls the Script() method on the Scripter object, passing in the set of tables in the database. The Scripter object is Microsoft’s top level object for managing scripting operations-see the MSDN reference page. The power of this class comes from its Options property, where you configure what it will do when you execute. The most important option is WithDependencies, which lets you automatically include all dependent objects in the generated script. Note that the first line creates or overwrites the specified file, giving you a clean slate, so to speak. The second line adds to that because the AppendToFile option is enabled. See all the options on the MSDN ScriptingOptions page.

This script is an adaptation of Edwin Sarmiento’s code in his blog post Generating SQL Scripts using Windows PowerShell, with two minor bug fixes. His article provides an excellent discussion of scripting DB objects with PowerShell for supplemental reading.

Conclusion: The Spectrum of PowerShell / SQL Server Entities

This article covered the full spectrum of SQL Server capabilities in PowerShell with broad strokes, providing a plethora of practical tips and techniques for making you productive quickly. The table below summarizes the concepts you have seen thus far and adds one more important one, SQLPSX, for exploration on your own.

(Both the StackOverflow question What’s the difference between PowerShell / SQL Server snap in’s / tools? and Chad Miller’s answer to it provided my inspiration for organizing this conclusion.)

Now that you’ve got a good foundation in PowerShell for SQL Server from this article, it is time to go out into the real world. A great place to start is PowerShell SMO: Just Writing Things Once, penned by the prolific Phil Factor, which walks you through real scenarios (with code) for managing multiple databases.