Navigating a SQL Server Instance with PowerShell

As well as navigating a folder structure with commands such as get-childitem, and set-location, PowerShell can also be used to navigate the SQL Server object hierarchy of an instance. You can connect PowerShell to the SQL Server database engine provider by using set-location to navigate to SQLSERVER:\SQL The information returned by get-childitem is dependent on the current location of the object hierarchy. The table below shows what information is returned from each level of the hierarchy.

Location	Information Returned
SQLSERVER:\SQL	The name of the local machine
SQLSERVER:\SQL\ComputerName	The names of the database engine instances installed on the local machine
SQLSERVER:\SQL\ComputerName\InstanceName	Instance level object types
Lower levels	Object types or objects contained within the current location

Once you have navigated to the level of the hierarchy, in which you are interested, you are able to use PowerShell to perform basic operations against objects at that level. For example, the script below will navigate to the "tables" namespace within the AdventureWorks2016 database and rename the dbo.DatabaseLog table, to dbo.DatabaseLogPS. The dir commands will display the original name and new name of the table.

Import-Module sqlps

sl SQLSERVER:\SQL\ESASSMgmt1\MASTERSERVER\DATABASES\ADVENTUREWORKS2016\TABLES

dir | where{$_.name -like "*DatabaseLog*"}

rename-item -LiteralPath dbo.DatabaseLog -NewName DatabaseLogPS

dir | where{$_.name -like "*DatabaseLog*"}

More complex tasks that do not have a specific cmdlet, associated with them can be performed by using the invoke-sqlcmd cmdlet. 

You can also start PowerShell from within SSMS. This is done by selecting "Start PowerShell" from the context menu of an object folder, within Object Explorer. This will cause the PowerShell CLI to be invoked, with the initial location being set to the object folder that you used to invoke the CLI.

My new book, Expert Scripting and Automation for SQL Server DBAs contains extensive details and examples of how to use PowerShell to administer SQL Server. The book is available from Apress at www.apress.com/9781484219423?gtmf=s or from Amazon, at www.amazon.com/Expert-Scripting-Automation-Server-DBAs/dp/1484219422?ie=UTF8&keywords=expert%20scripting%20and%20automation&qid=1465300485&ref_=sr_1_1&sr=8-1