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]
Go ahead and give it a try!