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%’.

, ,

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

  1. Robert L Davis March 22, 2011 at 8:36 am #

    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.

  2. Brad Schulz March 22, 2011 at 9:17 am #

    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

    • Kendra Little March 22, 2011 at 11:16 am #

      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.

      • Brad Schulz March 22, 2011 at 11:26 am #

        Okay… coming up.

        Look for it in an hour or so.

        –Brad

  3. Michael J Swart March 22, 2011 at 12:16 pm #

    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.

  4. sabyasm March 23, 2011 at 7:29 am #

    Excellent post – makes job much simpler. Thanks!

Trackbacks/Pingbacks

  1. Last Word On Scans | Michael J. Swart - March 23, 2011

    […] 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 […]

  2. Something for the Weekend – SQL Server Links 25/03/11 | John Sansom - SQL Server DBA in the UK - March 25, 2011

    […] 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. […]

Leave a Reply