While revising some training I was trying to get a current list of the DMVs in SQL 2019. Our documentation no longer provides the jumping off point page to see them. So the Toolman grabbed his circular saw, tape measure and here you go!
[sql]
— put in the subsystem you are interested in such as os, exec, or hadr
— we then identify the dmvs (and exclude DMFs) by using @SearchViews
— and parse out the rest of the definition by using @SearchTerm
DECLARE @SubSystem NVARCHAR(50) = N’os’;
DECLARE @SearchViews NVARCHAR(50) = N’%VIEW sys.dm[_]’ + @SubSystem + ‘%’;
DECLARE @SearchTerm NVARCHAR(50) = N’%’ + SUBSTRING(@SearchViews, 6, (LEN(@SearchViews)-5));
SELECT[dmv name] = SUBSTRING(definition, PATINDEX(@SearchTerm, definition)
— we have to search for ‘as’ plus whitespace to avoid ‘as’ within the dmv name
, PATINDEX(N’%AS[^0-9a-z]%’, definition) – PATINDEX(@SearchTerm, definition))
FROM
sys.all_sql_modules
WHERE definition LIKE @SearchViews;
GO [/sql]
Recent Comments