Products whose data or object models can be represented in a hierarchy use Windows PowerShell providers to expose the hierarchies. The hierarchy is exposed by using a drive and path structure similar to what the Windows file system uses.

Each Windows PowerShell provider implements one or more drives. Each drive is the root node of a hierarchy of related objects. The SQL Server provider implements a SQLSERVER: drive. The SQLSERVER: drive has four primary folders. Each folder and its subfolders represent the set of objects that can be accessed by using a SQL Server management object model. When you are focused on a subfolder in a path that starts with one of these primary folders, you can use the methods from the associated object model to perform actions on the object that is represented by the node. The Windows PowerShell folders implemented by the SQL Server 2008 provider are listed in the following table.

For example, you can use the SQLSERVER:\SQL folder to start paths that can represent any object that is supported by the SMO object model. The leading part of a SQLSERVER:\SQL path is SQLSERVER:\SQL\ComputerName\InstanceName. You must specify a computer name. You can specify either localhost or `(local`) for the local computer. You must always specify the instance name, even for default instances. For default instances, specify DEFAULT. The nodes after the instance name alternate between object classes (such as Database or View) and object names (such as AdventureWorks). Schemas are not represented as object classes. When you specify the node for a top-level object in a schema, such as a table or view, you must specify the object name in the format SchemaName.ObjectName.

This is the path of the Vendor table in the Purchasing schema of the AdventureWorks database in a default instance of the Database Engine on the local computer:

SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables\Purchasing.Vendor

For more information about the SMO object model hierarchy, see SMO Object Model Diagram.

Object class nodes in a path are associated with a collection class in the associated object model. Object name nodes are associated with an object class in the associated object model, as in the following table.

Whenever you reference an instance of the Database Engine in a path, the SQL Server provider uses SMO to open a Windows Authentication connection to the instance. The connection is made using the credentials of the Windows account running the Windows PowerShell session. The SQL Server provider does not use SQL Server Authentication.

Windows PowerShell implements cmdlets to navigate provider hierarchies and perform basic operations on the current object. Because the cmdlets are used frequently, they have short, canonical aliases. There is also one set of aliases that maps the cmdlets to similar command prompt commands, and another set for UNIX shell commands.

The SQL Server provider implements a subset of the provider cmdlets, shown in the following table.

For example, you can use one of the following sets of cmdlets or aliases to retrieve a listing of the SQL Server instances available to you by navigating to the SQLSERVER:\SQL folder and requesting the list of child items for the folder:

• Using full cmdlet names:
  
  Set-Location SQLSERVER:\SQL
  Get-ChildItem

• Using canonical aliases:
  
  sl SQLSERVER:\SQL
  gci

• Using cmd aliases:
  
  cd SQLSERVER:\SQL
  dir

• Using UNIX shell aliases:
  
  cd SQLSERVER:\SQL
  ls

  Important:
  Some SQL Server identifiers (object names) contain characters that Windows PowerShell does not support in path names. For more information about how to use names that contain these characters, see Using SQL Server Identifiers in PowerShell.

The information returned by Get-ChildItem (or its dir and ls aliases) depends on your location in a SQLSERVER: path.

By default, Get-ChildItem does not list any system objects. Use the Force parameter to see system objects, such as the objects in the sys schema.

This example lists the local computer and any computer to which you have made an SMO or WMI connection:

Set-Location SQLSERVER:\SQL
Get-ChildItem

This example lists the instances of the Database Engine on the local computer:

Set-Location SQLSERVER:\SQL\localhost
Get-ChildItem

This example lists the main classes of objects available in a default instance of the Database Engine. The list includes names like Endpoints, Certificates, and Databases:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT
Get-ChildItem

This example lists the databases available in a default instance of the Database Engine. The Force parameter is used to include the system databases like master and model:

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-ChildItem -force

After you have navigated to a node in a Windows PowerShell path, you can perform two types of actions:

• You can run Windows PowerShell cmdlets that operate on nodes, such as Rename-Item.
  
• You can call the methods from the associated SQL Server management object model, such as SMO. For example, if you navigate to the Databases node in a path, you can use the methods and properties of the Database class.
  

The SQL Server provider is used to manage the objects in an instance of the Database Engine. It is not used to work with the data in databases. If you have navigated to a table or view, you cannot use the provider to select, insert, update, or delete data. Use the Invoke-Sqlcmd cmdlet to query or change data in tables and views from the Windows PowerShell environment. For more information, see Using the Invoke-Sqlcmd cmdlet.

Listing Methods and Properties

[Microsoft.SqlServer.Management.SMO.Database] | Get-Member -Type Methods

$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar | Get-Member -Type Properties

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
Get-Item . | Get-Member -Type Properties

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks
Get-Item . | Get-Member -Type Properties

Using Methods and Properties

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Tables
Get-ChildItem | where {$_.Schema -eq "Sales"}

Remove-Item C:\PowerShell\CreateViews.sql
Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks\Views
foreach ($Item in Get-ChildItem) { $Item.Script() | Out-File -Filepath C:\PowerShell\CreateViews.sql -append }

Set-Location SQLSERVER:\SQL\localhost\DEFAULT\Databases
$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database
$MyDBVar.Parent = (Get-Item ..)
$MyDBVar.Name = "NewDB"
$MyDBVar.Create()
$MyDBVar.State

Windows PowerShell lets users define virtual drives, which are referred to as Windows PowerShell drives or ps drives. These map over the starting nodes of a path statement. They are typically used to shorten paths that are typed frequently. SQLSERVER: paths can get long, taking space in the Windows PowerShell window and requiring a lot of typing. If you are going to do a lot of work at a particular path node, you can define a custom Windows PowerShell drive that maps to that node. For example, if you are doing a lot of work in the AdventureWorks database, you can create an AWDB: drive:

New-PSDrive -Name AWDB -Root SQLSERVER:\SQL\localhost\DEFAULT\Databases\AdventureWorks

You can then use the AWDB: drive to shorten the path to AdventureWorks objects, such as the Purchasing.Vendor table:

Set-Location AWDB:\Tables\Purchasing.Vendor

By default, the SQL Server provider uses the Windows account under which it is running to make a Windows Authentication connection to the Database Engine. To make a SQL Server Authentication connection, you must associate the SQL Server login credentials with a virtual drive, and then use the change directory command (cd) to connect to that drive. In Windows PowerShell, security credentials can only be associated with virtual drives.

This script creates a function named sqldrive that you can use to create a virtual drive that is associated with the specified SQL Server Authentication login and instance.

function sqldrive
{
    param( [string]$name, [string]$login = "MyLogin", [string]$root = "SQLSERVER:\SQL\MyComputer\MyInstance" )
    $pwd = read-host -AsSecureString -Prompt "Password"
    $cred = new-object System.Management.Automation.PSCredential -argumentlist $login,$pwd
    New-PSDrive $name -PSProvider SqlServer -Root $root -Credential $cred -Scope 1
}

You can then create a virtual drive named SQLAuth: by running this command:

sqldrive SQLAuth

The sqldrive function prompts you to enter the password for your login, masking the password as you type it in. Then, whenever you use the change directory command (cd) to connect to a path by using the SQLAuth: drive, all operations are performed by using the SQL Server Authentication login credentials that you supplied when you created the drive.

In addition to the SQL Server Management Object Model namespaces that are associated with the \SQL, \SQLPolicy, and \SQLRegistration folders, you can also use classes in the Microsoft.SqlServer.Management.Smo.Wmi namespace. The most common use of this namespace is to query and manage the state of the services that implement each instance of the Database Engine or a policy store.

This example shows how to use the ManagedComputer class to stop and start the service running a default instance of the Database Engine.

# Get a reference to the ManagedComputer class.
cd SQLSERVER:\SQL\localhost
$Wmi = (get-item .).ManagedComputer
# Display the object properties.
$Wmi
# Get a reference to the default instance of the Database Engine.
$DfltInstance = $Wmi.Services["MSSQLSERVER"]
# Display the state of the service.
$DfltInstance
# Stop the service.
$DfltInstance.Stop(); write-host "Stopped"
# Refresh the cache and look at the state.
$DfltInstance.Refresh(); $DfltInstance
# Start the service again.
$DfltInstance.Start(); write-host "Started"

Note:
To use the classes in this namespace against remote computers, you must configure your Windows Firewall to allow WMI DCOM connections. For more information, see Configuring the Windows Firewall to Allow SQL Server Access.

Windows PowerShell tab-completion reduces the amount of typing you must do. When you have typed part of a path or cmdlet name, you can hit the Tab key to get a list of the items whose names match what you have already typed. You can then select the item you want from the list without having to type the rest of the name.

If you are working in a database that has a lot of objects, the tab-completion lists can become very large. Some SQL Server object types, such as views, also have large numbers of system objects.

The SQL Server snap-ins introduce three system variables that you can use to control the amount of information presented by tab-completion and Get-ChildItem.

$SqlServerMaximumTabCompletion = n

Specifies the maximum number of objects to include in a tab-completion list. If you select Tab at a path node having more than n objects, the tab-completion list is truncated at n. n is an integer. 0 is the default setting, and means there is no limit to the number of objects listed.

$SqlServerMaximumChildItems = n

Specifies the maximum number of objects displayed by Get-ChildItem. If Get-ChildItem is run at a path node having more than n objects, the list is truncated at n. n is an integer. 0 is the default setting, and means there is no limit to the number of objects listed.

$SqlServerIncludeSystemObjects = { $True | $False }

If $True, system objects are displayed by tab-completion and Get-ChildItem. If $False, no system objects are displayed. The default setting is $False.

The following example sets all three variables and lists their settings:

$SqlServerMaximumTabCompletion = 20
$SqlServerMaximumChildItems = 10
$SqlServerIncludeSystemObjects = $False
dir variable:sqlserver*

Concepts

Other Resources

Help and Information