QuickBlog: Finding all SQL Server DMV’s with PowerShell

Posted on by admin

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;
List SQL Server DMV's

Go ahead and give it a try!