PowerShell Essentials using SQL Server SMO

###———–IDERA GEEK SYNCH WebCast June 16th, 2015———————-##
### ——- Part – 1 PowerShell SMO by Maximo Trinidad ——————–##
###————————————————————————-##
### —- – Loads the SQL Server Assembly containing all classes that represent
### – the core SQL Server Database Engine objects:
[system.reflection.assembly]::LoadWithPartialName(“Microsoft.SQLServer.Smo”) | Out-Null;
### —- Connecting to SQL Server —– ##
## – Setting variables with Server Connect: (Default Windows Authentication)
$SQLSvrObj = new-object Microsoft.SqlServer.Management.Smo.Server ‘MTRINIDADLT2\MSSQL2K14′; `
$SQLSvrObj.ConnectionContext.ConnectTimeout = 21600; `
$SQLSvrObj.ConnectionContext.StatementTimeout = 0;

###——————————————————-##
### ———– Connecting to SQL Server using SQL Authentication ———– ##
###——————————————————-##
#region – SQL Authentication code block:
# <#
# $SQLUserName = “sa”; $sqlPwd = ‘$adm1n!’;
# $SQLSvrObj.ConnectionContext.LoginSecure = $false;
# $SQLSvrObj.ConnectionContext.set_Login($SQLUserName);
# $SqlUserPwd = ConvertTo-SecureString $sqlPwd -AsPlainText -Force;
# $SQLSvrObj.ConnectionContext.set_SecurePassword($SqlUserPwd);
# #>
#endregion
cls;

###——————————————————-##
### ———– Exploring your SQL Server objects ———– ##
###——————————————————-##
## – Look for information about SQL Server Engine:
$SQLSvrObj.Information;
## – Selecting some properties:
$SQLSvrObj.Information `
| Select-Object parent, Version, ProductLevel, edition `
| format-table -autosize;
Clear-Host
## – Exporing your PSObject
$SQLSvrObj | Get-Member | Out-GridView;
## – Looking only at values stored under properties:
$SQLSvrObj | Get-Member -MemberType ‘Properties’ | Out-GridView; `
$SQLSvrObj | Get-Member -MemberType ‘Methods’ | Out-GridView;
Clear-Host

###——————————————————-##
### —————- Looking at Databases ————— ##
###——————————————————-##
## – Selecting databases:
$SQLServerInstanceName = ‘MTRINIDADLT2\MSSQL2K14′; `
[Array] $SelectedDatabases = (($SQLSvrObj.Databases) | Select-Object Name `
| Out-GridView -PassThru -Title “SQL [$($SQLServerInstanceName)] – Select a database(s):”);
## – List selected database(s):
$SelectedDatabases | Select name;
Clear-Host;
## – Loop through each object found and store them under $Results:
[Array] $Results = ForEach ($db in $SelectedDatabases) `
{ `
$SQLSvrObj.Databases[$db.name] `
| Select-object Name, Owner, CreateDate, RecoveryModel, `
@{ Label = ‘Size(GB)’; Expression = { “{0:F}” -f (($_.Size) * 1MB/1GB) -as [float] }; }, `
@{ Label = ‘DataSpaceUsed(GB)’; Expression = { “{0:F}” -f (($_.dataspaceusage) * 1KB)/1GB -as [float] }; }, `
@{ Label = ‘SpaceAvailable(GB)’; Expression = { “{0:F}” -f (($_.spaceavailable) * 1KB)/1GB -as [float] }; }, `
@{ Label = ‘PercFree_%’; `
Expression = { “{0:N}” -f (((($_.spaceavailable) * 1KB)/1GB)/(($_.Size) * 1MB/1GB) * 100) -as [float] } }, `
@{ Label = ‘TodayDateTime’; Expression = { (Get-Date).ToString() }; } `
}; `
$Results | Format-Table -AutoSize;
## – Other output:
$Results | Out-GridView -Title “SQL Server: [$($SQLServerInstanceName)] – Database Information”;
## – Saving results to a file:
$repName1 = “c:\Temp\DatabaseListing_$((Get-Date).ToString(‘yyyyMMdd_hhmmss’)).csv”; `
$Results | Export-Csv -Path $repName1 -NoTypeInformation; Invoke-Item $repName1;
Clear-Host

###——————————————————-##
### —————— Looking at Tables —————- ##
###——————————————————-##
## – Create PSObject with database table information:
$DatabaseName = “AdventureWorks2014″; `
$dbtables = $SQLSvrObj.Databases[$DatabaseName].tables; `
$dbtables | Select name;
cls;
## 1 – Display Table Information on a GridView:
[array] $tblResults = $dbtables | Select-Object `
@{ Label = ‘SQLServerName’; Expression = { ($($SQLServerInstanceName)) }; }, `
@{ Label = ‘DatabaseName’; Expression = { ($_.Parent) }; }, `
@{ Label = ‘TableName’; Expression = { ($_.Name) }; }, `
@{ Name = ‘DataSpaceUsed(KB)’; Expression = { ($_.dataspaceused) }; }, `
@{ Label = ‘IndexSpaceUsed(KB)’; Expression = { ($_.IndexSpaceUsed) }; }, RowCount, `
@{ Label = ‘DateCreated’; Expression = { ($_.CreateDate) }; }, `
@{Name = “LastTableUpdate”; Expression = { `
$sqlupdtbl = ” `
Select `
last_user_update as [Last_Table_Update] `
from sys.dm_db_index_usage_stats `
where database_id = db_ID(‘$($DatabaseName)’) `
and Object_ID = Object_id(‘$($_.Name)’) `
“; `
$x = $SQLSvrObj.Databases[$($DatabaseName)].ExecuteWithResults($sqlupdtbl); `
(((($x.tables).getenumerator()) | Select $_.Last_Table_Update) `
| Select -first 1 Last_Table_Update).Last_Table_Update; `
}; `
};
###-Viewing Results:
$tblResults | ogv -t “SQL Server: [$($SQLServerInstanceName)] – Database [$($DatabaseName)] Tables Information”;
cls;

###———————————————————————–##
### —- SMO looking for Data MDF and Transac Log files UNC SQL Path —- ##
###———————————————————————–##
## – Listing Individual Database and Transaction logs file location:
## — List Database DATA file:
($SQLSvrObj.Databases[‘AdventureWorks2014′].FileGroups).files `
| Select-Object Name, filename, size, `
@{ Label = ‘Size(GB)’; Expression = { “{0:F}” -f (($_.size) * 1KB)/1GB -as [float] }; } `
| Format-List;
## — List LOG files:
($SQLSvrObj.Databases[‘AdventureWorks2014′].Logfiles) `
| Select-Object Name, filename, size, `
@{ Label = ‘Size(GB)’; Expression = { “{0:F}” -f (($_.size) * 1KB)/1GB -as [float] }; } `
| fl;
cls;
###——————————————————-##
## — List Data files all databases to a Gridview:
($SQLSvrObj.Databases.FileGroups).files `
| Select-Object Name, filename, size, `
@{ Label = ‘Size(GB)’; Expression = { “{0:F}” -f (($_.size) * 1KB)/1GB -as [float] }; } `
| Out-Gridview;
## — List LOG files all databases to a Gridview:
($SQLSvrObj.Databases.Logfiles) `
| Select-Object Name, filename, size, `
@{ Label = ‘Size(GB)’; Expression = { “{0:F}” -f (($_.size) * 1KB)/1GB -as [float] }; } `
| ogv;
cls;

###——————————————————-##
### ————— Building UNC Paths ——————-##
###——————————————————-##
## – Getting SQL Server name:
($SQLSvrObj.Information).NetName; `
$SQLSvrObj.Information.MasterDBPath; `
$SQLSvrObj.Information.MasterDBLogPath;
## – Mapping UNC drives:
New-PSDrive -Name M -PSProvider FileSystem -Persist -Root `
“\\$($SQLSvrObj.Information.NetName)\$(($SQLSvrObj.Information.MasterDBPath).replace(“:”, “$”))”; `
New-PSDrive -Name N -PSProvider FileSystem -Persist -Root `
“\\$($SQLSvrObj.Information.NetName)\$(($SQLSvrObj.Information.MasterDBLogPath).replace(“:”, “$”))”;
## End of Demo (Part 1) ##
cls;