Color Me Corrected – Will the REAL sysobjects please stand up?


A few days ago I posted a way to look at the Dynamic Management Views (DMV’s) using a query, which I then copy and paste into the Help viewer’s Index tab to learn more.

Well, my good friend and Colleague, Cliff, pointed out that I was using the older sysobjects system table. Well, of course this is a deprecated feature, and not the correct way to reference system objects in newer versions like SQL Server 2005 and higher.

I did that because I know a lot of you out there are still on SQL Server 2000 (shame on you!), but when he mentioned it I realized I hadn’t included the “correct” way to do this, using sys.system_objects, which has other benefits. It’s also a more global view, so you don’t have to USE master first, making the code shorter – and shorter code is often better! So here’s the more modern way do that:

SELECT

‘sys.’ + name

FROM

sys.system_objects

WHERE

name LIKE ‘dm%’;

GO

You can also toss in an ORDER BY name clause in there if you want to make the list sort.

Thanks Cliff –

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.

Skip to main content