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!

## - 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;

Go ahead and give it a try!