Dynamic Management Quickie: Exploring SQL Server’s system views and functions as you work

Note: If you like this post, here’s one you’re going to like even better! Check out DMV/DMF Info Just A Couple Clicks Away by Brad Schultz.

There’s a lot of dynamic management and system objects to keep track of in SQL Server.

We all sometimes have the moment when we can’t remember exactly which DMV, DMF or other system view/function returns a particular column, or if something even IS accessible from the system objects.

When this happens, remember that it’s easy to query system object and column names. Sys.system_columns and sys.system_objects are here to help.

Exploring the system views and functions yourself will also help you find new things.

This example shows all the system views and functions which are likely to have to do with CPU:

SELECT
SCHEMA_NAME(SCHEMA_ID)+ '.' + o.Name AS SysObjName,
o.type_desc,
c.name AS ColumnName
FROM sys.system_columns c
INNER JOIN sys.system_objects o ON o.object_id=c.object_id
WHERE c.name LIKE '%cpu%'

I like to use this version of the query, which includes the URL to look up more about the DMV. I like to use the browser in SSMS itself to look these up, so I include the shortcut for that in the header.

SELECT
	SCHEMA_NAME(SCHEMA_ID)+ '.' + o.Name AS SysObjName,
	o.type_desc,
	c.name AS ColumnName,
	'http://social.msdn.microsoft.com/Search/en-US/?Refinement=117&Query=' + SCHEMA_NAME(SCHEMA_ID)+ '.' + o.Name AS [Help! Ctrl + ALT + R to open web browser in SSMS]
FROM sys.system_columns c
INNER JOIN sys.system_objects o ON o.object_id=c.object_id
WHERE c.name LIKE '%page%'

The output looks like this (click for a larger view):

I’ve started using this lately since it helps me explore as well as remember.

Just when you think you know everything about the system objects, you’ll find something new.

Fun example: look at all the columns like ‘%page%’.

Previous Post
24 Hours of Rad: 24HOP Spring 2011 Recap
Next Post
There’s more than one way to skin an eggplant: Using APPLY for calculations

Related Posts

No results found

8 Comments. Leave new

Good recommendation!! My personal favorite is to simply look at the system views and functions until I find one that I’m not familiar with and then explore it to see what it is or what it does. I have found some very useful undocumented stuff that way.

Reply

Keep the quickies coming! Excellent post… Really useful.

BTW, I had to change the “ampersand A N D semicolon” to just an “ampersand” to get the URL to work in my browser. I have the feeling that you really just meant an “ampersand” but something happened (HTML-wise) in posting the code in the blog.

I have an idea to expand on this… Do you mind if I write a followup to this post?

–Brad

Reply

    Thanks Brad! My SyntaxHighlighter plugin is usually pretty good at escaping characters, but something went wrong here. I set the plugin back to its 2.x option fixed it– think it’s all better.

    I don’t mind at all if you do a follow up! I love the posts you did on XML hyperlinks. If you achieve the holy grail of getting a URL that’ll open in the browser in SSMS automatically, my life will be forever changed.

    Reply

Just tried it with the word “%scan%” input.
There are a lot more places where Microsoft uses the word scan than I thought.
I thought I got them all.

Thanks for posting.

Reply

Excellent post – makes job much simpler. Thanks!

Reply

[…] the meantime, thanks to Kendra Little’s script to search system tables, I discovered one more dmv that I had overlooked last week, sys.dm_db_index_operational_stats, and […]

Reply

[…] Dynamic Management Quickie: Exploring SQL Server’s system views and functions as you work – Great idea from Kendra Little (Blog|Twitter) with her T-SQL tip on how you can help remember which DMVs to use. […]

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu