Microsoft Windows PowerShell and SQL Server 2005 SMO - Part I - Page 2

June 6, 2007

Command Help

Inside Windows PowerShell, you can access the command list by typing the command:

Get-command

This displays all of the commands available in PowerShell. [Refer Fig 1.9]



Add-Content
Add-History
Add-Member
Add-PSSnapin
Clear-Content
Clear-Item
Clear-ItemProperty
Clear-Variable
Compare-Object
ConvertFrom-SecureString
Convert-Path
ConvertTo-Html
ConvertTo-SecureString
Copy-Item
Copy-ItemProperty
Export-Alias
Export-Clixml
Export-Console
Export-Csv
ForEach-Object
Format-Custom
Format-List
Format-Table
Format-Wide
Get-Acl
Get-Alias
Get-AuthenticodeSignature
Get-ChildItem
Get-Command
Get-Content
Get-Credential
Get-Culture
Get-Date
Get-EventLog
Get-ExecutionPolicy
Get-Help
Get-History
Get-Host
Get-Item
Get-ItemProperty
Get-Location
Get-Member
Get-PfxCertificate
Get-Process
Get-PSDrive
Get-PSProvider
Get-PSSnapin
Get-Service
Get-TraceSource
Get-UICulture
Get-Unique
Get-Variable
Get-WmiObject
Group-Object
Import-Alias
Import-Clixml
Import-Csv
Invoke-Expression
Invoke-History
Invoke-Item
Join-Path
Measure-Command
Measure-Object
Move-Item
Move-ItemProperty
New-Alias
New-Item
New-ItemProperty
New-Object
New-PSDrive
New-Service
New-TimeSpan
New-Variable
Out-Default
Out-File
Out-Host
Out-Null
Out-Printer
Out-String
Pop-Location
Push-Location
Read-Host
Remove-Item
Remove-ItemProperty
Remove-PSDrive
Remove-PSSnapin
Remove-Variable
Rename-Item
Rename-ItemProperty
Resolve-Path
Restart-Service
Resume-Service
Select-Object
Select-String
Set-Acl
Set-Alias
Set-AuthenticodeSignature
Set-Content
Set-Date
Set-ExecutionPolicy
Set-Item
Set-ItemProperty
Set-Location
Set-PSDebug
Set-Service
Set-TraceSource
Set-Variable
Sort-Object
Split-Path
Start-Service
Start-Sleep
Start-Transcript
Stop-Process
Stop-Service
Stop-Transcript
Suspend-Service
Tee-Object
Test-Path
Trace-Command
Update-FormatData
Update-TypeData
Where-Object
Write-Debug
Write-Error
Write-Host
Write-Output
Write-Progress
Write-Verbose
Write-Warning



Fig 1.9

A simple SMO in PowerShell

Let us query the sys.sydatabases of the SQL Server instance “SQLEXPRESS” from the host machine “HOME” using PowerShell and SMO.

Step 1

Go to the command prompt. Start – run – cmd

Step 2

Start PowerShell by typing the following command.

PowerShell

Step 3

Execute the following command, one by one, as shown below.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") "HOME\SQLEXPRESS"
foreach($database in $Server.databases) {$database.name}

You will see the results as shown below. [Refer Fig 2.0]


Fig 2.0

SMO Members

In order to display all of the members related to the object $Server, execute the following command as shown.

Step 1

Go to the command prompt. Start – run – cmd

Step 2

Start PowerShell by typing the following command:

PowerShell

Step 3

Execute the following commands, one by one, as shown below.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") "HOME\SQLEXPRESS"
$server | get-member

You will see the following results. [Refer Fig 2.1]

Alter
AttachDatabase
CompareUrn
DeleteBackupHistory
Deny
DetachDatabase
DetachedDatabaseInfo
EnumAvailableMedia
EnumCollations
EnumDatabaseMirrorWitnessRoles
EnumDetachedDatabaseFiles
EnumDetachedLogFiles
EnumDirectories
EnumErrorLogs
EnumLocks
EnumMembers
EnumObjectPermissions
EnumPerformanceCounters
EnumProcesses
EnumServerAttributes
EnumServerPermissions
EnumStartupProcedures
EnumWindowsDomainGroups
EnumWindowsGroupInfo
EnumWindowsUserInfo
Equals
GetActiveDBConnectionCount
GetDefaultInitFields
GetHashCode
GetPropertyNames
GetSmoObject
GetType
get_ActiveDirectory
get_BackupDevices
get_Configuration
get_ConnectionContext
get_Credentials
get_Databases
get_DefaultTextMode
get_Endpoints
get_Events
get_FullTextService
get_Information
get_InstanceName
get_JobServer
get_Languages
get_LinkedServers
get_Logins
get_Mail
get_Name
get_NotificationServices
get_Properties
get_ProxyAccount
get_ReplicationServer
get_Roles
get_ServiceMasterKey
get_Settings
get_State
get_SystemDataTypes
get_SystemMessages
get_Triggers
get_Urn
get_UserData
get_UserDefinedMessages
get_UserOptions
Grant
Initialize
IsDetachedPrimaryFile
IsWindowsGroupMember
KillAllProcesses
KillDatabase
KillProcess
PingSqlServerVersion
ReadErrorLog
Refresh
Revoke
SetDefaultInitFields
set_DefaultTextMode
set_UserData
ToString
ActiveDirectory
BackupDevices
Configuration
ConnectionContext
Credentials
Databases
DefaultTextMode
Endpoints
Events
FullTextService
Information
InstanceName
JobServer
Languages
LinkedServers
Logins
Mail
Name
NotificationServices
Properties
ProxyAccount
ReplicationServer
Roles
ServiceMasterKey
Settings
State
SystemDataTypes
SystemMessages
Triggers
Urn
UserData
UserDefinedMessages
UserOptions


Fig 2.1

Conclusion

As explained in the beginning of this article, this series is going to illustrate the power of Windows PowerShell in conjunction with SQL Server 2005. Part I of this series illustrated how to install and use a simple PowerShell command and a simple SMO command.

» See All Articles by Columnist MAK








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers