Ever wondered which dynamic management view gives you information about xxxxx?
e.g. you know that you want to find out which processes are currently blocking other processes (which was handled by the “blocked” column of “sysprocesses” in older SQL versions) but which view now holds this data?
If you simply join the system_views to the system_columns and filter on the column name (with a wild card) you will see all the dmv’s that contain that column name.
Using this technique you should be able to guess which of these is the view you want.
SELECT
AV.name
FROM sys.system_views AV
INNER JOIN sys.system_columns C ON AV.object_id = C.object_id
WHERE C.name like '%block%'
- dm_exec_requests
- dm_xe_sessions
- dm_os_waiting_tasks
- dm_os_waiting_tasks
- dm_os_waiting_tasks
- dm_resource_governor_workload_groups
- xml_schema_elements
- xml_schema_elements
- xml_schema_elements
- xml_schema_types
- xml_schema_types
- sysprocesses
The correct answer in this case is “dm_exec_requests”
If you want to join this to find out who the user is then query with filter = ‘Login’ and you’ll get ‘dm_exec_sessions’ in the list (and some other close but not relevant answers).
Its not an infallible method but it’s better than guessing and probably quicker than using help!
To speed things up more add this to your tools in SSMS
To speed things up more add this to your tools in SSMS
Create a stored procedure in your admin database if you have one (or put it in master db)
-- =============================================
-- Author:David Bridge
-- Create date:October 2011
-- Description:Find a DMV with a column name like this @SearchText
-- =============================================
CREATE PROCEDURE DBA_FindDMV
@SearchText varchar(128)
AS
BEGIN
SET NOCOUNT ON;
SELECT AV.name
FROM sys.system_views AV
INNER JOIN sys.system_columns C ON AV.object_id = C.object_id
WHERE C.name like '%' + @SearchText + '%'
END
Then add it in SSMS using menu:tools->options->environment->keyboard and put the fully qualified procedure name in the keyboard shortcuts. E.g. CTRL+4 = admin.dbo.DBA_FindDMV
Now in a query window just type the search text, highlight it and press CTRL+4
