Sql-server – SSMS 2014 can’t view Indexes on views

sql server 2014ssms

In SSMS 2008R2 you can navigate to a Database > Views > Find a specific view that you have an index on and unfurl the Index(es)

In SSMS 2014 (possibly other modern versions) you cannot. When you get to the View and unfurl, the selection of object types below it does not include Indexes – thus you cannot see the Indexes via the GUI. Of course I can still use TSQL to view the index, but this is not as easy to demonstrate to colleagues as via the GUI or for them to do themselves.

Does anyone know why this happens or if there is a way to allow the Indexes on Views to be displayed (I believe you cannot see the Indexes on any object within System Tables either, but that may be related to the same issue)? I'm guessing there is a setting in SSMS, but I haven't been able to find/answer via the net so far.

Steps to reproduce:

  1. Create a view (don't forget "WITH SCHEMABINDING AS" option)
  2. Create an Index against that view
  3. Navigate to your view within SSMS 2014
  4. When you unfurl that view (i.e. click the cross), there is not a "folder" for Indexes (I've just noticed, there isn't one for Columns either)

If you have access to SSMS from 2008R2 you will find that you can navigate to the Indexes on a View and also display the columns list.

Any ideas?

Best Answer

As J.D. suggested in the comments, try with the latest version of SSMS. I evaluated with 18.8 using the below:

USE tempdb
GO

CREATE VIEW dbo._IndexView WITH SCHEMABINDING

AS

SELECT 
1 AS One 
GO


CREATE UNIQUE CLUSTERED INDEX One ON dbo._IndexView (One)

--DROP VIEW dbo._IndexView

enter image description here

Related Question