xevent meta query

SELECT * FROM SYSOBJECTS WHERE NAME LIKE 'DM_XE_%' AND NAME NOT LIKE 'DM_XE_SESSION%'
GO

 

 

DECLARE @KEYWORD VARCHAR(100) = 'lock_escalation'
SELECT 'DM_XE_PACKAGES', * FROM SYS.DM_XE_PACKAGES WHERE NAME LIKE '%'+@KEYWORD+'%' OR DESCRIPTION LIKE '%'+@KEYWORD+'%'
SELECT 'DM_XE_OBJECTS',* FROM SYS.DM_XE_OBJECTS WHERE NAME LIKE '%'+@KEYWORD+'%' OR DESCRIPTION LIKE '%'+@KEYWORD+'%'
SELECT 'DM_XE_OBJECT_COLUMNS',* FROM SYS.DM_XE_OBJECT_COLUMNS WHERE NAME LIKE '%'+@KEYWORD+'%' OR DESCRIPTION LIKE '%'+@KEYWORD+'%'
SELECT 'DM_XE_MAP_VALUES',* FROM SYS.DM_XE_MAP_VALUES WHERE NAME LIKE '%'+@KEYWORD+'%' OR MAP_VALUE LIKE '%'+@KEYWORD+'%'
GO

 

 

SELECT P.NAME PACKAGE_NAME, O.NAME OBJECT_NAME, OBJECT_TYPE
  , OC.NAME OBJECT_COLUMNS_NAME, CASE COLUMN_TYPE WHEN 'DATA' THEN '=' WHEN 'CUSTOMIZABLE' THEN '=(1)' ELSE '' END PREDICATE_SRC
  , OC.TYPE_NAME OBJECT_COLUMN_TYPE_NAME, OC.COLUMN_TYPE OBJECT_COLUMN_TYPE
  , MV.MAP_KEY MAP_VALUES_KEY, MV.MAP_VALUE MAP_VALUES_MAP_VALUE
  FROM SYS.DM_XE_PACKAGES P
  LEFT JOIN SYS.DM_XE_OBJECTS O
    ON P.GUID = O.PACKAGE_GUID
  LEFT JOIN SYS.DM_XE_OBJECT_COLUMNS OC
    ON O.NAME = OC.OBJECT_NAME
   AND O.PACKAGE_GUID = OC.OBJECT_PACKAGE_GUID
  LEFT JOIN SYS.DM_XE_MAP_VALUES MV
    ON (OC.OBJECT_PACKAGE_GUID = MV.OBJECT_PACKAGE_GUID AND OC.TYPE_NAME = MV.NAME)
    OR (O.NAME = MV.NAME AND O.OBJECT_TYPE ='MAP')
 WHERE
 --  P.NAME = 'SQLOS'
   --AND P.DESCRIPTION =''
   --AND
   O.NAME ='LOCK_ESCALATION'
   --AND O.DESCRIPTION = ''
   --AND OC.NAME = 'MAX_MEMORY_GRANT'
   --AND OC.DESCRIPTION = ''
   --AND MV.NAME = 'lock_escalation_cause'
   --AND MV.MAP_VALUE = ''
 ORDER BY
    P.NAME
  , O.NAME
  , CASE OC.COLUMN_TYPE WHEN 'READONLY' THEN 1 WHEN 'CUSTOMIZABLE' THEN 2 WHEN 'DATA' THEN 3 ELSE NULL END 
  , OC.COLUMN_ID
  , MV.NAME
  , MV.MAP_KEY