Comments (2)

  1. Tim Ford says:

    You could also identify DMVs (Dynamic Management Views) v. DMFs (Dynamic Management Functions) via the following:

    SELECT ‘sys.’ + [name],[type]

    FROM sys.[system_objects]

    WHERE [name] LIKE ‘dm%’

    ORDER BY [type];

    Type = ‘IF’ = (inline) function (DMF)

    Type = ‘V’ = (view) DMV

  2. Both of the code samples work, but they make an assumption about the schema being ‘sys.’ that may not always be accurate.  It is better to use the SCHEMA_NAME() function with the schema_id to create the FQN of the object:

    SELECT SCHEMA_NAME(schema_id) + ‘.’ + name

    FROM sys.system_objects

    WHERE name LIKE ‘dm%’;

    This code is repeatable for any of the objects including the INFORMATION_SCHEMA views.