Compter les DMV – Scripts Center

Compte les DMV par catégorie. Syntaxe SQL Server 2016+, utilise la fonction string_split.

/*-------------------------------------------------------------------
[SCRIPT] count_dmvs   
[DATABASE] master
[DESCRIPTION] Count DMV by categories
[MAJ PAR] DATAFLY - Arian Papillon
-------------------------------------------------------------------*/
USE master
GO
SELECT  value AS Categorie
      , type_desc
      , COUNT(*) AS Nb
FROM    ( SELECT    ROW_NUMBER() OVER ( PARTITION BY name ORDER BY name ) AS part
                  , name
                  , value
                  , type_desc
          FROM      sys.system_objects
                    CROSS APPLY STRING_SPLIT(name, '_')
          WHERE     LEFT(name, 2) = 'dm'
        ) AS x
WHERE   part = 2
GROUP BY GROUPING SETS(( value, type_desc ), type_desc)
ORDER BY GROUPING(value)
      , type_desc DESC;

/*-------------------------------------------------------------------

[SCRIPT] count_dmvs

[DATABASE] master

[DESCRIPTION] Count DMV by categories

[MAJ PAR] DATAFLY - Arian Papillon

-------------------------------------------------------------------*/

USE master

SELECT value AS Categorie

, type_desc

, COUNT(*) AS Nb

FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY name ORDER BY name ) AS part

, name

, value

, type_desc

FROM sys.system_objects

CROSS APPLY STRING_SPLIT(name, '_')

WHERE LEFT(name, 2) = 'dm'

) AS x

WHERE part = 2

GROUP BY GROUPING SETS(( value, type_desc ), type_desc)

ORDER BY GROUPING(value)

, type_desc DESC;