{"id":1384,"date":"2012-08-09T00:00:00","date_gmt":"2012-08-09T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/practical-powershell-for-sql-server-developers-and-dbas-part-2\/"},"modified":"2021-08-24T13:40:07","modified_gmt":"2021-08-24T13:40:07","slug":"practical-powershell-for-sql-server-developers-and-dbas-part-2","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/practical-powershell-for-sql-server-developers-and-dbas-part-2\/","title":{"rendered":"Practical PowerShell for SQL Server Developers and DBAs &#8211; Part 2"},"content":{"rendered":"<div id=\"pretty\">\n<ul class=\"series-articles\">\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/database-administration\/practical-powershell-for-sql-server-developers-and-dbas-part-1\/\">Practical PowerShell for SQL Server Developers and DBAs &#8211; Part 1<\/a><\/li>\n<li class=\"series-articles--active\">\n<p>Practical PowerShell for SQL Server Developers and DBAs &#8211; Part 2<\/p>\n<\/li>\n<\/ul>\n<h2>Contents<\/h2>\n<ul>\n<li><a href=\"#navigating\">Navigating in SQL Server Space<\/a>\n<ul>\n<li><a href=\"#datastores\">Data Stores and Drives<\/a><\/li>\n<li><a href=\"#nodes\">Nodes in SQL Server Space<\/a><\/li>\n<li><a href=\"#cmdlets\">Cmdlets Implemented by the SQL Server Provider<\/a><\/li>\n<li><a href=\"#mappeddrives\">Mapped Drives<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#sqlserverspace\">Working in SQL Server Space<\/a>\n<ul>\n<li><a href=\"#understanding\">Understanding and Displaying Nodes<\/a><\/li>\n<li><a href=\"#rowcounts\">Finding Meta-Information: Row Counts<\/a><\/li>\n<li><a href=\"#dbobjects\">Finding Meta-Information: Scripting DB Objects<\/a><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#conclusion\">Conclusion: The Spectrum of PowerShell \/ SQL Server Entities<\/a><\/li>\n<\/ul>\n<p class=\"start\">If you have not done so already, <a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/database-administration\/practical-powershell-for-sql-server-developers-and-dbas-part-1\/\">please review Part 1<\/a> to get the most out of this article. Also <a href=\"http:\/\/www.simple-talk.com\/content\/file.ashx?file=7234\">see the accompanying wallchart<\/a> (link starts PDF download) that distills the key details out of both parts into a one-page reference.<\/p>\n<h2 id=\"navigating\">Navigating in SQL Server Space<\/h2>\n<h3 id=\"datastores\">Data Stores and Drives<\/h3>\n<p>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 &#8220;SQL Server space&#8221; from the command line in a similar way that you would use SSMS&#8217;s object explorer and context menus. Before tackling this capability, however, you need to have an appreciation for PowerShell <em>data stores<\/em>. First, realize that these <em>data stores<\/em> are quite distinct from SQL Server <em>databases<\/em>. Each PowerShell data store is managed by a <em>PowerShell provider<\/em> (see the PowerShell help page <a href=\"http:\/\/technet.microsoft.com\/en-us\/library\/dd347659.aspx\">about_providers<\/a> for more details). If you run the <strong>Get-PSProvider<\/strong> cmdlet you will see a list similar to this:<\/p>\n<div>\n<table class=\"MsoTableMediumShading1Accent3\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b> Name<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b> Capabilities<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b> Drives<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>WSMan<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Credentials<\/p>\n<\/td>\n<td valign=\"top\">\n<p>{WSMan}<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Alias<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>ShouldProcess<\/p>\n<\/td>\n<td valign=\"top\">\n<p>{Alias}<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Environment<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>ShouldProcess<\/p>\n<\/td>\n<td valign=\"top\">\n<p>{Env}<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>FileSystem<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Filter, ShouldProcess<\/p>\n<\/td>\n<td valign=\"top\">\n<p>{C, D, E, F}<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Function<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>ShouldProcess<\/p>\n<\/td>\n<td valign=\"top\">\n<p>{Function}<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Registry<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>ShouldProcess, Transactions<\/p>\n<\/td>\n<td valign=\"top\">\n<p>{HKLM, HKCU}<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Variable<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>ShouldProcess<\/p>\n<\/td>\n<td valign=\"top\">\n<p>{Variable}<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Certificate<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>ShouldProcess<\/p>\n<\/td>\n<td valign=\"top\">\n<p>{cert}<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>SqlServer<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Credentials<\/p>\n<\/td>\n<td valign=\"top\">\n<p>{SQLSERVER, DB}<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>Start with the most familiar provider, the <strong>FileSystem provider<\/strong>. On my system it manages four drives, the ubiquitous <strong>C:<\/strong> drive along with the <strong>D:<\/strong>, <strong>E:<\/strong>, and <strong>F:<\/strong> 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:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"> Set-Location C:\r\nSet-Location Documents\r\nSet-Location C:\\usr\\tmp\r\nchdir C:\\usr\\tmp # alias for Set-Location\r\ncd C:\\usr\\tmp # alias for Set-Location\r\nsl C:\\usr\\tmp # alias for Set-Location\r\n<\/pre>\n<p>Once you go to a particular location, you can list the contents using the <strong>Get-ChildItem<\/strong> cmdlet (aliases: dir, gci, ls). Or, as you might expect, you could specify a path to <strong>Get-ChildItem<\/strong> without changing your current location (e.g. ls C:\\usr\\tmp).<\/p>\n<p>PowerShell has abstracted this concept in a novel and powerful way to other data stores. Notice, for example, that there is a <em>Registry provider<\/em>, which by default provides two &#8220;drives&#8221;, the <strong>HKLM:<\/strong> and <strong>HKCU:<\/strong> drives. Just as you would expect, you can set your location to either of these drives, then navigate the registry tree with the same <strong>Set-Location<\/strong> 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:).<\/p>\n<p>Now take a look at the <em>SqlServer provider<\/em>, 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 <strong>sqlps<\/strong> 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.<\/p>\n<h3 id=\"nodes\">Nodes in SQL Server Space<\/h3>\n<p>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 <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/cc281947.aspx\">SQL Server PowerShell provider<\/a> page on MSDN for more details on these other object types.<\/p>\n<p>The table below specifies the paths to some of the locations you will most commonly use.<\/p>\n<div>\n<table class=\"MsoTableMediumShading1Accent3\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\"><b> Description<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\"><b> Node<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">SQL Server data store root<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">SQLSERVER:\\<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">Network root<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">SQLSERVER:\\SQL<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">Instances on selected machine<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">SQLSERVER:\\SQL\\<i>machine<\/i><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">Top-level objects in selected instance<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">SQLSERVER:\\SQL\\<i>machine\\instance<\/i><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">Databases in selected instance<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">SQLSERVER:\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">Top-level objects in selected database<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">SQLSERVER:\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">Tables in selected database<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">SQLSERVER:\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i>\\Tables<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">Views in selected database<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">SQLSERVER:\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i>\\Views<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">Roles in selected database<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">SQLSERVER:\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i>\\Roles<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">Triggers in selected database<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">SQLSERVER:\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i>\\Triggers<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>When navigating in <em>file system space<\/em>, 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 <em>SQL Server space<\/em> you have the same options. You can, for instance, Set-Location SQLSERVER:\\SQL\\localhost or you could <strong>Get-ChildItem<\/strong> 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 <strong>localhost<\/strong> to refer to your current machine.<\/p>\n<p>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:<\/p>\n<div>\n<table class=\"table--bare table--tight\">\n<thead>\n<tr>\n<td><strong>Get-ChildItem<\/strong> sqlserver:\\sql<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>MachineName<\/td>\n<\/tr>\n<tr>\n<td>&#8212;&#8212;&#8212;&#8211;<\/td>\n<\/tr>\n<tr>\n<td>gandalf<\/td>\n<\/tr>\n<tr>\n<td>localhost<\/td>\n<\/tr>\n<tr>\n<td>server509<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>This example also illustrates a PowerShell feature that is not obvious (until you see it the first time): <em>the output of a cmdlet varies based on the provider<\/em>. In contrast to the output of the SQL Server provider on the \\SQL node, for the file system provider <strong>Get-ChildItem<\/strong> returns output like this, showing properties you would expect for files and folders:<\/p>\n<pre>Mode\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0LastWriteTime\u00a0\u00a0\u00a0\u00a0\u00a0Length Name\r\n----\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0-------------\u00a0\u00a0\u00a0\u00a0\u00a0------ ----\r\nd----\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a05\/21\/2012\u00a0\u00a0\u00a09:58 AM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Modules\r\n-a---\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06\/12\/2012\u00a0\u00a0\u00a06:44 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0911 Microsoft.PowerShell_profile.ps1\r\n-a---\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a06\/18\/2012\u00a0\u00a0\u00a05:42 PM\u00a0\u00a0\u00a0\u00a0\u00a0\u00a033392 pshist.xml<\/pre>\n<p>Furthermore, <strong>Get-ChildItem<\/strong> could even return <em>different<\/em> output within a <em>single<\/em> provider based on context. The table below lists the same common SQL Server paths shown earlier, this time listing their default output from <strong>Get-ChildItem<\/strong>. Most of the time <strong>Get-ChildItem<\/strong> 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&#8217; 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.<\/p>\n<div>\n<table class=\"MsoTableMediumShading1Accent3\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\"><b> Node<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\"><b> Default Properties<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Name, Root, Description<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">MachineName<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">InstanceName<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine\\instance<\/i><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\"><b><i>list of object names<\/i><\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Name, Status, RecoveryModel, CompatLvl, Collation, Owner<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\"><b><i>list of object names<\/i><\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i>\\Tables<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Schema, Name, Created<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i>\\Views<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Schema, Name, Created<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i>\\Roles<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Name<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i>\\Triggers<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Name, Created<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>The table lists the <em>default<\/em> properties displayed for each type of object when you use <strong>Get-ChildItem<\/strong> 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 <strong>Get-ChildItem<\/strong>.<\/p>\n<h3 id=\"cmdlets\">Cmdlets Implemented by the SQL Server Provider<\/h3>\n<p>You have already seen examples of PowerShell cmdlets that are implicitly supported by the SQL Server provider: <strong>Get-ChildItem<\/strong> and <strong>Set-Location<\/strong>. There are several other important cmdlets supported, providing important functionality:<\/p>\n<div>\n<table class=\"MsoTableMediumShading1Accent3\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b> Cmdlet<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b> Canonical alias<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b> Other aliases<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b> Description<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b> Get-Location<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>gl<\/p>\n<\/td>\n<td valign=\"top\">\n<p>pwd<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Gets current node<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b> Set-Location<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>sl<\/p>\n<\/td>\n<td valign=\"top\">\n<p>cd, chdir<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Changes current node<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b> Get-ChildItem<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>gci<\/p>\n<\/td>\n<td valign=\"top\">\n<p>dir, ls<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Lists the objects at current node<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b> Get-Item<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>gi<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\n<p>Properties of current node<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b> Rename-Item<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>rni<\/p>\n<\/td>\n<td valign=\"top\">\n<p>ren<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Renames an object<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b> Remove-Item<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>ri<\/p>\n<\/td>\n<td valign=\"top\">\n<p>del, erase, rd, rm, rmdir<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Removes an object<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<h3 id=\"mappeddrives\">Mapped Drives<\/h3>\n<p>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:<\/p>\n<pre> net use DriveLetter: \\\\ComputerName\\Path <\/pre>\n<p>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:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">   New-PSDrive-Name name -PSProvider SQLSERVER -Root root <\/pre>\n<p>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 <strong>New-PSDrive<\/strong> 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:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">    mount sandboxDB SQLSERVER SQLSERVER:\\SQL\\localhost\\DEFAULT\\Databases\\sandbox <\/pre>\n<p>That command allows you to reference&#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"> Get-ChildItem sandboxDB:\\Tables <\/pre>\n<p>instead of&#8230;<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">   Get-ChildItem SQLSERVER:\\SQL\\localhost\\DEFAULT\\Databases\\sandbox\\Tables <\/pre>\n<p>Earlier you saw the output of the <strong>Get-PSProvider<\/strong> cmdlet listing current drives by provider. You can get a more detailed view of the drives themselves with the <strong>Get-PSDrives<\/strong> cmdlet:<\/p>\n<div>\n<table class=\"MsoTableMediumShading1Accent3\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b> Name<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b> Provider<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b> Root<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b> Current Location<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Alias<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Alias<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>C<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>FileSystem<\/p>\n<\/td>\n<td valign=\"top\">\n<p>C:\\<\/p>\n<\/td>\n<td valign=\"top\">\n<p>usr\\tmp<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>cert<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Certificate<\/p>\n<\/td>\n<td valign=\"top\">\n<p>\\<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>D<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>FileSystem<\/p>\n<\/td>\n<td valign=\"top\">\n<p>D:\\<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<tr class=\"color--red\">\n<td valign=\"top\">\n<p><b>DB<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>SqlServer<\/p>\n<\/td>\n<td valign=\"top\">\n<p>SQLSERVER:\\sql\\localhost\\SQLEXPRESS\\Databases<\/p>\n<\/td>\n<td valign=\"top\">\n<p>sandbox<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>E<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>FileSystem<\/p>\n<\/td>\n<td valign=\"top\">\n<p>E:\\<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Env<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Environment<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>F<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>FileSystem<\/p>\n<\/td>\n<td valign=\"top\">\n<p>F:\\<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Function<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Function<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>HKCU<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Registry<\/p>\n<\/td>\n<td valign=\"top\">\n<p>HKEY_CURRENT_USER<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>HKLM<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Registry<\/p>\n<\/td>\n<td valign=\"top\">\n<p>HKEY_LOCAL_MACHINE<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<tr class=\"color--red\">\n<td valign=\"top\">\n<p><b> SQLSERVER<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>SqlServer<\/p>\n<\/td>\n<td valign=\"top\">\n<p>SQLSERVER:\\<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>Variable<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Variable<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b>WSMan<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>WSMan<\/p>\n<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<td valign=\"top\">\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>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 <strong>Set-Location<\/strong>).<\/p>\n<h2 id=\"sqlserverspace\">Working in SQL Server Space<\/h2>\n<h3 id=\"understanding\">Understanding and Displaying Nodes<\/h3>\n<p>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.<\/p>\n<div>\n<table class=\"MsoTableMediumShading1Accent3\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\"><b> Node<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\"><b> Object Type<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Microsoft.SqlServer.Management.PowerShell.Extensions. <br \/>\n SqlServerProviderExtension<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Microsoft.SqlServer.Management.PowerShell.Extensions.Machine<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Microsoft.SqlServer.Management.Smo.Server<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine\\instance<\/i><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\"><i>System.String<\/i><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Microsoft.SqlServer.Management.Smo.Database<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i><\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\"><i>System.String<\/i><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i>\\Tables<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Microsoft.SqlServer.Management.Smo.Table<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i>\\Views<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Microsoft.SqlServer.Management.Smo.View<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i>\\Roles<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Microsoft.SqlServer.Management.Smo.DatabaseRole<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p class=\"auto-style1\">\\SQL\\<i>machine<\/i>\\<i>instance<\/i>\\Databases\\<i>database<\/i>\\Triggers<\/p>\n<\/td>\n<td valign=\"top\">\n<p class=\"auto-style1\">Microsoft.SqlServer.Management.Smo.Trigger<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>The object types were determined from this simple PowerShell sequence:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">  @(Get-ChildItem node)[0].GetType().FullName <\/pre>\n<p>For example:<\/p>\n<div>\n<table class=\"table--bare table--tight\">\n<thead>\n<tr>\n<td>@(<strong>Get-ChildItem<\/strong> SQLSERVER:\\sql\\localhost\\sqlexpress\\databases)[0].GetType().FullName<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Microsoft.SqlServer.Management.Smo.Database<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>Each object may specify a <em>default<\/em> set of properties but this is often not the <em>complete<\/em> 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 <em>formatting file<\/em>, which specifies both default properties and default formatting on individual .NET types. The main PowerShell system directory lists standard types but some modules, including <strong>sqlps<\/strong>, 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 <strong>DefaultDisplayPropertySet<\/strong> 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.<\/p>\n<div>\n<table class=\"auto-style2\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p>Type specified in <b> module-specific<\/b> formatting file?<\/p>\n<\/td>\n<td valign=\"top\">\n<p>$env:windir\\system32\\ WindowsPowerShell \\v1.0\\Modules\\*. format.ps1xml<\/p>\n<p>$HOME\\Documents\\WindowsPowerShell\\Modules\\*.format.ps1xml<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>Type specified in <b>system<\/b> formatting file?<\/p>\n<\/td>\n<td valign=\"top\">\n<p>$env:windir\\system32\\ WindowsPowerShell \\v1.0\\*.format.ps1xml<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>DefaultDisplayPropertySet defined?<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Use $<i>object<\/i>.PSStandardMembers.DefaultDisplayPropertySet; otherwise, display all properties<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>After the default property set is determined, the final determination of how to display objects is a choice between <strong>Format-List<\/strong> and <strong>Format-Table<\/strong>. You can always direct output to one of these specifically as the tail of your pipeline to mandate that format. Here&#8217;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.<\/p>\n<div>\n<table class=\"table--bare table--tight\">\n<thead>\n<tr>\n<td><strong>Get-ChildItem<\/strong> sqlserver:\\sql | <strong>Format-Table<\/strong> <em>-Force<\/em> *<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>PSPath\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0PSParentPath\u00a0\u00a0PSChildName\u00a0\u00a0PSDrive\u00a0\u00a0\u00a0\u00a0\u00a0PSProvider\u00a0\u00a0PSIsContainer MachineName &#8212;&#8212;\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0&#8212;&#8212;&#8212;&#8212;\u00a0\u00a0&#8212;&#8212;&#8212;&#8211;\u00a0\u00a0&#8212;&#8212;-\u00a0\u00a0\u00a0\u00a0\u00a0&#8212;&#8212;&#8212;-\u00a0\u00a0&#8212;&#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;&#8211; SqlServer:&#8230; SqlServer:&#8230; gandalf\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0SQLSERVER\u00a0\u00a0\u00a0SqlServer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0True gandalf SqlServer:&#8230; SqlServer:&#8230; localhost\u00a0\u00a0\u00a0\u00a0SQLSERVER\u00a0\u00a0\u00a0SqlServer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0True localhost SqlServer:&#8230; SqlServer:&#8230; server509\u00a0\u00a0\u00a0\u00a0SQLSERVER\u00a0\u00a0\u00a0SqlServer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0True server509<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>The truncation <em>within<\/em> 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 <strong>Format-Table<\/strong> puts an entire <em>record<\/em> 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<strong>-List<\/strong>, which displays one <em>property<\/em> per line, giving plenty of room to see the property&#8217;s value. Here are the first couple objects above now given ample room:<\/p>\n<div>\n<table class=\"table--tight table--bare\">\n<thead>\n<tr>\n<td><strong>Get-ChildItem<\/strong> sqlserver:\\sql | <strong>Format-List<\/strong> <em>-Force<\/em> *<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>PSPath\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0: SqlServer::SQLSERVER:\\sql\\gandalf PSParentPath\u00a0\u00a0\u00a0\u00a0: SqlServer::SQLSERVER:\\sql PSChildName\u00a0\u00a0\u00a0\u00a0\u00a0: gandalf PSDrive\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0: SQLSERVER PSProvider\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0: SqlServer PSIsContainer\u00a0\u00a0\u00a0: True MachineName\u00a0\u00a0\u00a0\u00a0\u00a0: gandalf ManagedComputer\u00a0: Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer Servers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0: {[DEFAULT, Microsoft.SqlServer.Management.PowerShell.Extensions.ServerInformation]} PSPath\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0: SqlServer::SQLSERVER:\\sql\\localhost PSParentPath\u00a0\u00a0\u00a0\u00a0: SqlServer::SQLSERVER:\\sql PSChildName\u00a0\u00a0\u00a0\u00a0\u00a0: localhost PSDrive\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0: SQLSERVER PSProvider\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0: SqlServer PSIsContainer\u00a0\u00a0\u00a0: True MachineName\u00a0\u00a0\u00a0\u00a0\u00a0: localhost ManagedComputer\u00a0: Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer Servers\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0: {[SQLEXPRESS, Microsoft.SqlServer.Management.PowerShell.Extensions.ServerInformation]} strong&gt;. . . (truncated)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>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 <strong>Format-List<\/strong> or <strong>Format-Table<\/strong> boils down to the number of properties to display: if more than four it uses <strong>Format-List<\/strong>, with four or less it uses <strong>Format-Table<\/strong>. Here&#8217;s a summary of the steps:<\/p>\n<div>\n<table class=\"auto-style2\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p>Format-verb cmdlet specified in pipeline?<\/p>\n<\/td>\n<td valign=\"top\">\n<p>&#8230; | Format-List &#8230;<\/p>\n<p>&#8230; | Format-Table &#8230;<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>Type specified in <b> module-specific<\/b> formatting file?<\/p>\n<\/td>\n<td valign=\"top\">\n<p>$env:windir\\system32\\ WindowsPowerShell \\v1.0\\Modules\\*. format.ps1xml<\/p>\n<p>$HOME\\Documents\\WindowsPowerShell\\Modules\\*.format.ps1xml<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>Type specified in <b>system<\/b> formatting file?<\/p>\n<\/td>\n<td valign=\"top\">\n<p>$env:windir\\system32\\ WindowsPowerShell \\v1.0\\*.format.ps1xml<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>More than four properties?<\/p>\n<\/td>\n<td valign=\"top\">\n<p>Use <b>Format-List<\/b>; otherwise, use <b> Format-Table<\/b><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<h3 id=\"rowcounts\">Finding Meta-Information: Row Counts<\/h3>\n<p>The <strong>Invoke-SqlCmd<\/strong> 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 <em>about<\/em> 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: <a href=\"http:\/\/stackoverflow.com\/questions\/2221555\/how-to-fetch-the-row-count-for-all-the-tables-in-a-sql-server-database\">how to fetch the row count for all the tables in a SQL Server database<\/a>. One of the most straightforward and concise, posted by Adrian Banks, is this:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE #counts\r\n(\r\n\u00a0\u00a0\u00a0\u00a0table_name VARCHAR(255),\r\n\u00a0\u00a0\u00a0\u00a0row_count INT\r\n)\r\n\r\nEXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'\r\nSELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC <\/pre>\n<p>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:<\/p>\n<ol>\n<li><em>Performance<\/em>: 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.<\/li>\n<li><em>Stability<\/em>: sp_MSForEachTable is an <a href=\"http:\/\/www.databasejournal.com\/features\/mssql\/article.php\/3441031\/SQL-Server-Undocumented-Stored-Procedures-spMSforeachtable-and-spMSforeachdb.htm\">undocumented stored procedure<\/a>, so you should not rely on it.<\/li>\n<li><em>Resource use<\/em>: it uses a temporary table; not a big thing, but a solution that does not use one would be better.<\/li>\n<\/ol>\n<p>In contrast, one of the best in terms of performance and resources, posted by Keng, is this:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SELECT o.name,\r\n\u00a0\u00a0ddps.row_count \r\nFROM sys.indexes AS i\r\n\u00a0\u00a0INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID\r\n\u00a0\u00a0INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID\r\n\u00a0\u00a0AND i.index_id = ddps.index_id \r\nWHERE i.index_id &lt; 2\u00a0\u00a0AND o.is_ms_shipped = 0 ORDER BY o.NAME <\/pre>\n<p>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.<\/p>\n<p>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 <strong>Get-Member<\/strong> cmdlet to reveal all the properties for tables:<\/p>\n<div>\n<table class=\"table--bare table--tight\">\n<thead>\n<tr>\n<td><strong>Get-ChildItem<\/strong> sandboxDB:\\Tables | Get-Member -type properties<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0MemberType\u00a0\u00a0\u00a0Definition &#8212;-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0&#8212;&#8212;&#8212;-\u00a0\u00a0\u00a0&#8212;&#8212;&#8212;- DisplayName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NoteProperty System.String DisplayName=dbo.BinaryDataTest PSChildName\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NoteProperty System.String PSChildName=dbo.BinaryDataTest PSDrive\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NoteProperty System.Management.Automation.PSDriveInfo P&#8230; PSIsContainer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NoteProperty System.Boolean PSIsContainer=True PSParentPath\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NoteProperty System.String PSParentPath=SqlServer::SQLS&#8230; PSPath\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NoteProperty System.String PSPath=SqlServer::SQLSERVER:&#8230; PSProvider\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0NoteProperty System.Management.Automation.ProviderInfo &#8230; AnsiNullsStatus\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Property\u00a0\u00a0\u00a0\u00a0\u00a0System.Boolean AnsiNullsStatus {get;set;} ChangeTrackingEnabled\u00a0\u00a0\u00a0\u00a0Property\u00a0\u00a0\u00a0\u00a0\u00a0System.Boolean ChangeTrackingEnabled {get;&#8230; Checks\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Property\u00a0\u00a0\u00a0\u00a0\u00a0Microsoft.SqlServer.Management.Smo.CheckCo&#8230; strong&gt;. . . (truncated)<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>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 <strong>count<\/strong>. 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 <em>aliases<\/em> for each cmdlet in the above sequence: <strong>gci<\/strong> for <strong>Get-ChildItem<\/strong>, <strong>gm<\/strong> for <strong>Get-Member<\/strong>, and <strong>?<\/strong> for <strong>Where<\/strong><strong>-Object<\/strong>. (Aliases are quite useful when you are typing commands interactively as well!)<\/p>\n<div>\n<table class=\"table--tight table--bare\">\n<thead>\n<tr>\n<td><strong>gci<\/strong> sandboxDB:\\Tables | gm -type properties | ? { $_.name -match &#8216;count&#8217; }<\/td>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0MemberType Definition &#8212;-\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0&#8212;&#8212;&#8212;- &#8212;&#8212;&#8212;- RowCount\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Property\u00a0\u00a0\u00a0System.Int64 RowCount {get;} RowCountAsDouble Property\u00a0\u00a0\u00a0System.Double RowCountAsDouble {get;}<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>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 <strong>Get-ChildItem<\/strong>, select the table name and the table row count with <strong>Select-Object<\/strong>, and pipe the results through <strong>Format-Table<\/strong> to provide a cleaner output:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">  Get-ChildItem sandboxDB:\\Tables | Select-Object Name,RowCount | Format-Table -AutoSize <\/pre>\n<p>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 <strong>Where-Object<\/strong> used just above to filter the property list. This command assumes there are a number of tables containing the word Big in the name:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\"> gci sandboxDB:\\Tables | ?{$_.name -match 'Big'}| select Name,RowCount | ft- AutoSize <\/pre>\n<p>Similarly, you could select based on schema with something like this:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">   gci sandboxDB:\\Tables | where {$_.Schema -eq \"dbo\"} ... <\/pre>\n<h3 id=\"dbobjects\">Finding Meta-Information: Scripting DB Objects<\/h3>\n<p>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.<\/p>\n<p>First consider the question of generating a script for one or more tables in your database. In fact, Stack Overflow presents this very question: <a href=\"http:\/\/stackoverflow.com\/questions\/21547\/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table\">How do I generate a CREATE TABLE statement for a given table?<\/a> The answers posted there suggest a number of variations, all of which are fairly complex. In contrast, here is the <em>complete<\/em> code to generate table creation scripts for <em>all<\/em> tables in your database (of course, substitute the appropriate path in SQL Server space to <em>your<\/em> database):<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">  Get-ChildItem sandboxDB:\\Tables | ForEach-Object { $_.Script() } <\/pre>\n<p>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:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">   gci sandboxDB:\\Tables | % { $_.Script() } | Set-Content C:\\create.sql <\/pre>\n<p>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):<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">   gci sandboxDB:\\Tables | % { $_.Script() + \"GO\" } <\/pre>\n<p>Here is a summary of these and other variations. I again use aliases here for brevity (<strong>gci<\/strong> for <strong>Get-ChildItem<\/strong>,<strong>?<\/strong> for <strong>Where<\/strong><strong>-Object<\/strong>, and <strong>%<\/strong> for <strong>ForEach-Object<\/strong>):<\/p>\n<div>\n<table class=\"auto-style2\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b>Description<\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p><b>Command sequence<\/b><\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>All tables (output to console)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b> gci<\/b> sandboxDB:\\Tables | <b> %<\/b> { $_.Script() }<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>All tables (output to file)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b> gci<\/b> sandboxDB:\\Tables | <b> %<\/b> { $_.Script() } | <b> Set-Content<\/b> C:\\create.sql<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>All tables (separate batches)<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b> gci<\/b> sandboxDB:\\Tables | <b> %<\/b> { $_.Script() + &#8220;GO&#8221; }<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>Selected tables<\/p>\n<\/td>\n<td valign=\"top\">\n<p><b> gci<\/b> sandboxDB:\\Tables | ? { $_.name -match &#8220;big.*&#8221; } | <b> %<\/b> { $_.Script() }<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>Single table<\/p>\n<\/td>\n<td valign=\"top\">\n<p>(<b>gci<\/b> sandboxDB:\\Tables | ? { $_.name -eq &#8220;xyz_table&#8221; }).Script()<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p>All tables with their indexes<\/p>\n<\/td>\n<td valign=\"top\"><code> <b> gci<\/b> sandboxDB:\\Tables | <b> %<\/b> { \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $_.Script() + \"GO\" \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 $_.Indexes | <b> %<\/b> { $_.Script() + \"GO\" } } <\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>As you can appreciate, a very little PowerShell can accomplish quite a lot. But wait! Incredible as it may seem, this is actually the &#8220;low-level&#8221; way to script your database objects. At a higher level, you do not even need to worry about the &#8220;GO&#8221; statements, the associated objects, or even the looping. The brief script below, as you will observe, is mostly setting the various options on the <strong>Scripter<\/strong> object, plus a few introductory lines defining a couple variables. Beyond the bookkeeping all that remains is two lines of code, highlighted below:<\/p>\n<pre class=\"theme:ssms2012 lang:tsql mark:5,19 decode:true \">$myScriptFile = \"C:\\usr\\tmp\\scripts.sql\"&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;# Specify your output file\r\n$myDbInstance = gi sqlserver:\\sql\\localhost\\sqlexpress&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;# Specify your server\r\n$mydb = $myDbInstance.Databases[\"sandbox\"]&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;# Specify your DB\r\n\r\n$mydb.Script() | Out-File $myScriptFile&amp;nbsp;&amp;nbsp;&amp;nbsp;&amp;nbsp;# &lt;&lt;&lt;&lt;&lt; 1\r\n\r\n$scrp = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($myDbInstance)\r\n$scrp.Options.AppendToFile = $True\r\n$scrp.Options.ClusteredIndexes = $True\r\n$scrp.Options.DriAll = $True\r\n$scrp.Options.ScriptDrops = $False\r\n$scrp.Options.IncludeIfNotExists = $True\r\n$scrp.Options.IncludeHeaders = $True\r\n$scrp.Options.ToFileOnly = $True\r\n$scrp.Options.Indexes = $True\r\n$scrp.Options.WithDependencies = $True\r\n$scrp.Options.FileName = $myScriptFile\r\n\r\n$scrp.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$mydb.Tables)&amp;nbsp;&amp;nbsp;&amp;nbsp;# &lt;&lt;&lt;&lt;&lt; 2 <\/pre>\n<p>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 <strong>Scripter<\/strong> object, passing in the set of tables in the database. The <strong>Scripter<\/strong> object is Microsoft&#8217;s top level object for managing scripting operations-see the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/microsoft.sqlserver.management.smo.scripter.aspx\">MSDN reference page<\/a>. The power of this class comes from its <strong>Options<\/strong> property, where you configure what it will do when you execute. The most important option is <strong>WithDependencies<\/strong>, 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 <strong>AppendToFile<\/strong> option is enabled. See all the options on the MSDN <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/microsoft.sqlserver.management.smo.scriptingoptions.aspx\">ScriptingOptions page<\/a>.<\/p>\n<p>This script is an adaptation of Edwin Sarmiento&#8217;s code in his blog post <a href=\"http:\/\/www.mssqltips.com\/tip.asp?tip=1842\">Generating SQL Scripts using Windows PowerShell<\/a>, with two minor bug fixes. His article provides an excellent discussion of scripting DB objects with PowerShell for supplemental reading.<\/p>\n<h3 id=\"conclusion\">Conclusion: The Spectrum of PowerShell \/ SQL Server Entities<\/h3>\n<p>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.<\/p>\n<div>\n<table class=\"MsoTableMediumShading1Accent3\">\n<tbody>\n<tr>\n<td valign=\"top\">\n<p><b> <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/ms162169.aspx\">SMO<\/a><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>Short for <b>SQL Server Management Objects<\/b>, SMO is a set of .NET classes to create applications that manage SQL Server. All of the nodes in SQL Server space discussed herein resolve to SMO objects (e.g. database tables are Microsoft.SqlServer.Management.Smo.Table objects). SMO was introduced in SQL Server 2005.<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b> <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/hh231286.aspx\"> SQLPS<\/a><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>This PowerShell module provides (1) several new cmdlets-notably Invoke-SqlCmd-that allow you to interact with a database, and (2) the SQL Server provider with which you can navigate SQL Server space. This module was included with SQL Server 2012 but, thanks to an eponymous package wrapped up by Chad Miller, may just as easily be used with earlier SQL Server editions. (Not to complicate the issue, there is also a <b>sqlps<\/b> utility provided in SQL Server 2008 that runs a &#8220;mini&#8221; PowerShell with SQL support, but this utility is deprecated in favor of the <b>sqlps<\/b> module.)<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b> <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/cc281947.aspx\">SQL Server Provider<\/a><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>This provider allows you to interact with the hierarchy of SQL Server objects just as a native PowerShell file system provider allows you to interact with files. You can navigate through SQL Server space using paths analogously to file paths. As MSDN explains, &#8220;You can use the paths to locate an object, and then use methods from the SQL Server Management Object (SMO) models to perform actions on the objects.&#8221;<\/p>\n<\/td>\n<\/tr>\n<tr>\n<td valign=\"top\">\n<p><b> <a href=\"http:\/\/sqlpsx.codeplex.com\/\">SQLPSX<\/a><\/b><\/p>\n<\/td>\n<td valign=\"top\">\n<p>This is Chad Miller&#8217;s CodePlex project first published in 2008, prior to SQL Server having any PowerShell support. Despite the later support added by Microsoft via the <b>sqlps<\/b> module, SQLPSX&#8217;s wide range of SQL-related functions still provide functionality not covered by <b> sqlps<\/b>. Quoting the home page of the project, &#8220;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.&#8221;<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n<p>(Both the StackOverflow question <a href=\"http:\/\/stackoverflow.com\/questions\/9500556\/difference-between-powershell-sql-server-snap-ins-tools\">What&#8217;s the difference between PowerShell \/ SQL Server snap in&#8217;s \/ tools?<\/a> and Chad Miller&#8217;s answer to it provided my inspiration for organizing this conclusion.)<\/p>\n<p>Now that you&#8217;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 <a href=\"http:\/\/www.simple-talk.com\/sql\/database-administration\/powershell-smo-just-writing-things-once\/\">PowerShell SMO: Just Writing Things Once<\/a>, penned by the prolific Phil Factor, which walks you through real scenarios (with code) for managing multiple databases.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Having shown just how useful PowerShell can be for DBAs in executing queries, Michael Sorens now takes us through navigating SQL Server space and finding meta-information &#8211; valuable information for anyone looking to be more productive in SQL Server.&hellip;<\/p>\n","protected":false},"author":221868,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143527],"tags":[],"coauthors":[6802],"class_list":["post-1384","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1384","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221868"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=1384"}],"version-history":[{"count":20,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1384\/revisions"}],"predecessor-version":[{"id":75020,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/1384\/revisions\/75020"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=1384"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=1384"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=1384"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=1384"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}