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

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

Rate This
  • Comments 2

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 -

Leave a Comment
  • Please add 8 and 1 and type the answer here:
  • Post
  • 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

  • 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.

Page 1 of 1 (2 items)