The above output shows that a new drive ( SQLSERVER:\. ) is available which is the root drive.To navigate to it and and query the child items use the below command:

Set-Location SQLSERVER:
Get-ChildItem | Select-Object Name

The above output shows the following child items:

• SQL
• SQLPolicy
• SQLRegistration
• DataCollection

To navigate to a subfolder, use the cmdlet Set-Location, as shown below:

Set-Location SQL
Get-ChildItem

Each folder in the hierarchy contains different methods which can be invoked and different properties you can access and set values to. To find all the methods and properties available in a folder, use the Get-item and Get-member cmdlets as shown below:

Set-Location SQLSERVER:\SQL
Set-Location machine_name\DEFAULT\Databases
Get-Item . | Get-Member -Type Methods

In addition, you can get server information about the default instance using the Get-Item cmdlet and its properties using the below code:

Set-Location SQLSERVER:\SQL\machine_name\
$server = Get-Item Default
$server.get_VersionString()
$Server.Settings.Properties | Select-Object Name, Value | Format-Table –auto

The Invoke-Sqlcmd Cmdlet

The SQL Server PowerShell provider includes the cmdlet Invoke-Sqlcmd which is extremely useful for executing ad hoc queries and stored procedures.

For example, to query all the data from the Address table in AdventureWorks2008 execute the below code:

Set-Location SQLSERVER:\SQL\machine_name\DEFAULT\Databases\AdventureWorks2008
Invoke-Sqlcmd -Query "select top 20 * from Person.Address" | format-table –Auto

To get all the methods and properties available for an object returned from Invoke-Sqlcmd use the Get-Member cmdlet. The below example uses the parameter –ServerInstance with the cmdlet Get-Item. In the below code (Get-Item .) gets the current server instance name .

Set-Location SQLSERVER:\SQL\machine_name\DEFAULT\
$myTable =Invoke-Sqlcmd -Query "SELECT * from master.dbo.sysobjects " -ServerInstance
(Get-Item .)
$mMyTtable | get-member

You can access the row information using the Foreach-Object cmdlet as in the below code sample:

$myTable =Invoke-Sqlcmd -Query "SELECT * from master.dbo.sysdatabases "
-ServerInstance (Get-Item .)
$Mytable| Foreach-Object {$_.db_id;$_.name;$_.crdate;} | Format-Table -Auto

Using SQL Server Management Objects with PowerShell

You can leverage the SQL Server SMO class library via PowerShell to connect to SQL Server and retrieve and manipulate data.

Before using SMO objects, you will need to load the SMO class library (ie Microsoft.SqlServer.Smo.dll).   The LoadWithPartialName method can load an assembly from the application directory or from the GAC using a partial name. You can then connect to a SQL Server instance using SQL Server SMO using the below code:

[System.Reflection.Assembly]::LoadWithPartialName(’Microsoft.SqlServer.SMO’) |
Out-Null
$smovar = New-Object (’Microsoft.SqlServer.Management.Smo.Server’)
‘machine_name\SQL2008’

Using SMO you can connect to a database and perform any operations that you would normally perform in SSMS. As a simple example the below commands create the database “dummydb” on the SQL Server default instance using SMO:

$Server = New-Object Microsoft.SqlServer.Management.Smo.Server("machine_name")
$database = New-Object Microsoft.SqlServer.Management.Smo.Database($server,"dummydb")
$database.create()