SSMS Short cut to get information from DMO without Stored Procedure

Article
1/17/2024

Problem Definition

A recent forum question asked for a method to create a shortcut in SQL SERVER management studio to get information from Dynamic Management Objects without creating a stored procedure. This wiki article aims at providing an answer to this question.

Scenario 1

If we want to get the index detail ( name,type_desc, is_unique) of the a table from sys.indexes & sys.objects like below query on pressing a SSMS short cut on the table name, we also don't want to create any stored procedure in SQL SERVER, As we may be connecting many SQL SERVER

SELECT i.name,i.type_desc,i.is_unique FROM  sys.indexes i JOIN  sys.objects o
ON i.object_id = o.object_id WHERE o.name = '<tablename>'

Scenario 2

If we want to get the index details(indexname,index_type_desc,index_level, avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count) of the a table from sys.dm_db_index_physical_stats like below query on pressing a SSMS short cut on the table name

SELECT OBJECT_NAME(OBJECT_ID) indexname,index_type_desc,index_level,
2.avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count FROM
3.sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('<objectname>'), NULL,
4.NULL , 'SAMPLED')
5.ORDER BY  avg_fragmentation_in_percent DESC

Resolution

How I came to this solution

Assume we already have shortcut (CTRL+F1) key assigned for sp_helptext
Then have enable the SQL Profiler in my local SQL SERVER and capture the query when we highlight a text (say stored procedure name) in sql query window and hit the shortcut key (CTRL+F1)
From the SQL Profiler I got below text - no single or double quotes, I have highlighted the table name (sp1) and hit (CTRL+F1)

sp_helptext sp1

We need to pass the parameter (table or stored procedure name) without single or double quotes like in sp_helptext system procedure, so I have declare the parameter type with nvarchar as in sp_helptext and with the help of sp_executesql.

Scenario 1 Solution

To get the index details (name,type_desc, is_unique) of a table from sys.indexes & sys.objects by pressing a SSMS short cut on the table name:

Add the below sql text in any SSMS shortcuts (Tools->Options->Environment->Keyboard->Query Shortcuts), close and reopen SSMS.
No need to enclose below text with any single or double quote, and the text should end with equal(=).

EXEC sp_executesql N'SELECT i.name,i.type_desc,i.is_unique FROM sys.indexes i 
JOIN sys.objects o ON i.object_id = o.object_id WHERE o.name = 
@objname',N'@objname nvarchar(776)',@objname =

Open a query window now, and type the table name you want then select the table name and hit your shortcut key that you have assigned to get the index details from DMO as you want.

Exception : if the selected object is not in default schema(dbo) like below, you may need to select the object with single quote with schema name to make this work
schema1.table1 ->'schema1.table1'

It works the same way as system stored procedure(sp_help)
Highlight any table above with quotes then Press shortcut(Ctrl+F1)

Scenario 2 Solution

Please follow the same steps like scenario 1, just you need to give the below SQL text in SSMS shortcuts (Tools->Options->Environment->Keyboard->Query Shortcuts) instead the one give in scenario 1 to get index details from sys.dm_db_index_physical_stats

EXEC sp_executesql N'SELECT OBJECT_NAME(OBJECT_ID) 
indexname,index_type_desc,index_level, 
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count FROM 
sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(@objname), NULL, NULL , 
''SAMPLED'') ORDER BY avg_fragmentation_in_percent DESC',N'@objname 
nvarchar(776)',@objname =

Feel free to make changes according to your need to make use

Share via