Powershell Programming: sql server smo

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