Powershell Programming: sql server smo

I am in the process of putting my notes into web pages, please keep checking in later for more contents

Powershell Version

SQL Server PowerShell (SQLPS)

SQL Server Management Objects (SMO) smo 

.NET (System.Data.SqlClient) sqlclient 

Invoke-SqlCmd invoke_sqlcmd 


$PSVersionTable
$PSVersionTable.PSVersion

Get-Help Get-Member -full;
$MySQLObject | gm | more;

Set-ExecutionPolicy RemoteSigned

Get-Command -Module SQLPS
Get-Command -Module SQLSERVER

Get-Module -ListAvailable
Import-Module -Name SQLPS
Import-Module sqlps -DisableNameChecking 
Import-Module -Name SqlServer
$profile

%ProgramFiles%\WindowsPowerShell\Modules\SqlServer


powershell 5: Windows Management Framework 5.0


get-help install-module

update-help install-module

get-module -listavailable

install-module sqlserver

import-module sqlserver


Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=13.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=11.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"

Add-Type -Path "C:\Program Files\System.Data.SQLite\2010\bin\System.Data.SQLite.dll"
Add-Type -AssemblyName "Microsoft.SqlServer.Smo"

Add-Type -path `
"C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll"


load smo in sql server 2008R2 

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
       [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
       [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlWmiManagement")

# -- ----------
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server "localhost\sql12"
$srv.Databases | select name
# -- -----------
# SQL Server Management Objects (SMO)
$cred = Get-Credential
Add-Type -AssemblyName "Microsoft.SqlServer.Smo,Version=13.0.0.0,Culture=neutral,PublicKeyToken=89845dcd8080cc91"
$srv = New-Object Microsoft.SqlServer.Management.Smo.Server MyServer
# $srv = New-Object('Microsoft.SqlServer.Management.Smo.Server') MyServer

$srv.ConnectionContext.LoginSecure = $true
$srv.ConnectionContext.ConnectAsUser = $true

# $SQLSvr = "."
# $SQLSvr = "myservername"
# $MySQLObject = new-object Microsoft.SqlServer.Management.Smo.Server $SQLSvr;;

$MySQLObject = new-object  Microsoft.SqlServer.Management.Smo.Server "myservername"
$MySQLObject
$MySQLObject.Information

$MySQLObject|select-object *

$MySQLObject.Information | Select Parent, Product, Edition, VersionString | FT -auto;

$MySQLObject.databases
$MySQLObject.logins

$MySQLObject.physicalmemory
$MySQLObject.servicename
$MySQLObject.HostPlatform
$MySQLObject.DatabaseEngineEdition
$MySQLObject.edition
$MySQLObject.product
$MySQLObject.productlevel
$MySQLObject.tcpenabled

# alter table person.person alter column lastname null;

$srv = new-object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = $srv.Databases.Item("AdventureWorks2012")
$tb = $db.Tables.Item("Person", "Person")
$col = $tb.Columns.Item("LastName")
$col.Nullable = $TRUE
$col.Alter()

# -- list databases

$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "servername\instancename"

foreach($sqlDatabase in $sqlServer.databases) {$sqlDatabase.name}

$sqlServer | get-member | more

# join an array into a string

-- ---------------------------------------------------------------------------
$myArray = "file1.csv","file2.csv"
$a = ($myArray -join ",")
$a
-- ---------------------------------------------------------------------------
[array]$myArray = '"file1.csv"','"file2.csv"'
[string]$a = $null

$a = $myArray -join ","

$a
-- ---------------------------------------------------------------------------
($net_final | Select-Object -Unique) -Join ","

($net_final|group|?{$_.count -ge 5}|Select -ExpandProperty Name) -join ","
-- ---------------------------------------------------------------------------
$myArray = "file1.csv","file2.csv"

# Solution with single quote
$a = "'$($myArray -join "','")'"
$a
# Result = 'file1.csv','file2.csv'

# Solution with double quotes
$b = '"{0}"' -f ($myArray -join '","')
$b
# Result = "file1.csv","file2.csv"

Reference

sql server smo 
msdn Powershell