Views on SQL DW are not visible in SSMS and in [INFORMATION_SCHEMA].[VIEWS]

Question

I just created a view using below definition

create view dbo.dm_pdw_exec_requests_hist_view as
select t1.*,login_name,app_name,client_id from sys.dm_pdw_exec_requests t1 
left outer join [sys].[dm_pdw_exec_sessions] t2 on 
t1.request_id=t2.request_id

and the view was created successfully.

but I was not able to see the view in the SSMS tool [ Under views section]

and also - when I tried to query [INFORMATION_SCHEMA].[VIEWS] , there also i couldnt see a entry for the view dm_pdw_exec_requests_hist_view

select * from [INFORMATION_SCHEMA].[VIEWS] 

But if I do select * from dm_pdw_exec_requests_hist_view ; it is returning the results as expected.

So - is there any restriction in viewing the definition of a view which contains DMVs in it ?

Thanks, Aravind

Answer 1

This is very odd. I'm able to repo your behavior that the view doesn't show up in our DMVs (sys.objects or sys.views). This is the defect as SSMS which uses those views isn't aware of it. I do see that creating and then running the view works (sample below) so functionally this is working. I also tried renaming the view to dbo.Matt (creative, I know) and this worked. I've filed a defect to figure out the reserved name issue and we can document this for now.

CREATE VIEW dbo.dm_pdw_exec_requests_hist_view
AS
    select
        t1.*,
        login_name,
        app_name,
        client_id
    from
        sys.dm_pdw_exec_requests t1 
        left outer join [sys].[dm_pdw_exec_sessions] t2 on t1.request_id=t2.request_id;
GO

SELECT * FROM dbo.dm_pdw_exec_requests_hist_view
GO

Answer 2

this is a very interesting case.

At first I thought maybe you're using an older version of SSMS, because I can see VIEWS in my SSMS. But then I tried to create your View and this one doesn't show up. Even querying sys.objects or sys.all_objects doesn't show that the View exists.

I have no idea what is going on here, but suggest you open a support case.

Markus