QuickBlog: Finding all SQL Server DMV’s with PowerShell

Execute the following PowerShell with script SMO to quickly list all your Dynamic Management View’s from your SQL Server instance.  Let’s do this!

[sourcecode language=”powershell”]
## – Loads the SQL Server SMO Assembly:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo")

## – Initialize the variable and loads the SQL Server objects:
$SQLServer = ‘SQLServerInstanceName’;
$mySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLServer;
## Get some basic information:
$MySQLINfo = $mySQL.Information | Select Netname, Product, Edition;

## 1. List all SQL Server views:
$mysql.Databases[‘master’].views | Select schema, name | ft -AutoSize;

## 2. List all SQL Server Dynamic Management Views:
$mysql.Databases[‘master’].views | where{$_.name -match ‘dm_’} `
| Select schema, name | ft -AutoSize;

## 3. Add the SQL Server Information with the Dynamic Management Views:
$mysql.Databases[‘master’].views | where{$_.name -match ‘dm_’} `
| Select `
@{label=’Netname’;Expression={$MySQLInfo.NetName}}, `
@{label=’Product’;Expression={$MySQLInfo.Product}}, `
@{label=’Edition’;Expression={$MySQLInfo.Edition}}, `
schema, name | ft -AutoSize;
[/sourcecode]

List SQL Server DMV's

Go ahead and give it a try!

PowerShell working with SQL Server SMO Month…

It’s SQL Server SMO week and I’ll be presenting at the next Space Coast SQL Server User Group on Thursday October 11th at 6:30PM. For more information check out the following #SCSUG link: http://spacecoast.sqlpass.org/

Also, I’m restarting the Florida PowerShell User Group virtual meeting this coming Friday October 12th at 11:30am rescheduled t0 October 26th due to equipment problems with the same topic: “PowerShell working with SQL Server SMO“. I will be providing the virtual meeting link on Friday morning before the meeting at my http://www.FLPSUG.com site.

Stay tuned for more information.