buck.woody
LinkedIn | FaceBook | Twitter
Resume
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
FROM
WHERE
GO You can also toss in an ORDER BY name clause in there if you want to make the list sort.
Thanks Cliff -
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.
Professional Blog
Personal Blog
Podcasts
Articles
University of Washington