SQL Server SMO in PowerShell

LoadWithPartialName

[reflection.assembly]::loadwithpartialname(“Microsoft.SqlServer.Smo”) | out-null

pipe with out-null to avoid output.

New-Object

$srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’)

pipe with gm (get member)

sidebar:

$a = dir
$a will display directory

now if we do $a | gm it will have properties for directory. Like move to, Extension, Encrypted etc.

$srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’)

$srv| gm

This will show members for SQL Server (like Databases, Alter, Revoke, DetachDatabase, ConnectionContext etc.)

Passing the server name:

$srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) “ServerName\InstanceName”

Preferred method, assign in variables and pass variable. e.g.

$inst = “Servername\InstanceName”

$srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $inst

Authentication:

SMO Object Model Diagram

https://docs.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/smo-object-model-diagram?view=sql-server-ver15

$srv.ConnectionContext

$srv.databases

(this takes time)

$srv.databases | FT Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable -auto

FT: Formatted Table

Restricting the result to one database (myDB):

$srv.databases[“myDB”] | FT Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable -auto

Get info from the list of specific databases (i.e. those starting with SL)

$srv.databases | ?{ $_.Name -match “^SL”} | FT Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable -auto

To send data in GridView (UI component), use Select instead of FT and pipe with out-GridView

$srv.databases | ?{ $_.Name -match “^SL”} | Select Name, Size, DataSpaceUsage, IndexSpaceUsage, SpaceAvailable | out-GridView

Get Table names

$srv.databases[“MyDB”].Tables.Name

Get Names of tables starting with KM

$srv.databases[“MyDB”].Tables | ?{$_.Name -match “^KM”} | FT Name

Script the table definition

$srv.databases[“MyDB”].Tables[“KM_MyTable”].Script()

Job Server

$srv.jobServer | gm

$srv.JobServer.Jobs.Name

Limiting Objects

Get From my list of selected databases, get list of all databases that have a table co_mst

$DBlist = "MyDB1", "MyDB2", "MyDB3";

$DBList | %{

    $DBName = $_;
    IF ($srv.databases[$DBName].Tables.Contains("co_mst","dbo"))
    {
        "$DBName"
    }
}

Following script parse a list of databases. identifies if a specific table is in the database or not. If its there, then get more properties for it.

$DBlist = "MyDB1", "MyDB2", "MyDB3";

$results = @();

$DBList | %{

    $DBName = $_;
    [int]$Exists = $srv.databases[$DBName].Tables.Contains("MyTableName");
    [int]$Size = $srv.databases[$DBName].Tables["MyTableName"].Size;
    [int]$DataSpace = $srv.databases[$DBName].Tables["MyTableName"].DataSpaceUsed;


        $row = @{}

        $row["DBName"] = $DBName;
        $row["Exists"] = $Exists;
        $row["Size"]   = $Size;
        $row["DataSpace"] = $DataSpace;
        $results += New-Object psobject -property $row
    }
$results | Select DBName, Exists,DataSpace,Size;

Methods

Alter >> Use Alter whenever you are changing any data and Alter is available in the methods. Otherwise it will modify only in session and will not write it to DB.

Scripting

Following script, scripts structure and data of a table.

[reflection.assembly]::loadwithpartialname("Microsoft.SQLServer.Smo") | out-null

$srv = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) "Server/Instance"
$so = New-Object('Microsoft.SqlServer.Management.Smo.ScriptingOptions');
$s = New-Object('Microsoft.SqlServer.Management.Smo.Scripter') ($srv);

$BasePath = "C:\MyFolder";
$FileName = "$BasePath\Tables.txt";
$Begin = Get-Date;

If (Test-Path $FileName)
{
    del $FileName;
}
$TableList = $srv.databases["DB_Name"].Tables;

$s.Options.ScriptData = 1;

$TableList | ?{$_.Name -eq "tbl_name"} | %{

    $s.EnumScript($_) | Out-File "$FileName" -Append;
    $_.Name;
}
notepad.exe $FileName;

Comments

comments