Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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 '%cpu%'

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

Comments

Posted by Anonymous on 23 March 2011

Pingback from  Dew Drop – March 23, 2011 | Alvin Ashcraft's Morning Dew

Posted by Jerrry Brenner on 23 March 2011

This is great!  Thanks.  What I'd really like is a query that returns a link to the appropriate page in BOL for every DMV.  I've adapted the query to return a search page for every DMV, which is a reasonable compromise.  Oracle provides metadata tables for wait events (and other things).  I suggested this to Microsoft, but it got rejected.  I guess I could use the same trick for that, but some of them are so poorly documented that it may not help.

Leave a Comment

Please register or log in to leave a comment.