June 24, 2019 at 7:46 pm
My manager tasked me to find all materialized views in our database. In SQL Server i guess it translates to "indexed" views. But anyway, I checked sys.views, sys.objects and there is nothing there that can indicate it, all views are shown the same V. Is there is anything else in dictionary objects that I should join to?
Thanks
June 24, 2019 at 7:51 pm
A materialised view, in my experience, is a view which has been saved/persisted to a table. This is often for reasons of performance, but could also be because the data on which the view depends is being refreshed or modified and there is a desire to physically store the 'before changes' version of the data.
So I would suggest that you need to double-check what your manager meant, before continuing.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
June 24, 2019 at 8:05 pm
You can use the objectproperty function to see if a view (or table) is indexed - along the lines of:
SELECT *
FROM sys.objects
WHERE type='V'
and OBJECTPROPERTY(object_id,'IsIndexed')=1
Sue
June 24, 2019 at 8:13 pm
We have a stored procedure, that:
1. Deletes object_a
2. insert into object_a
select from object_b
Because step 2 takes a long time (like 10 minutes), ones it refreshed object_a, all reports select their data from object_a. He insists that at least one of them surely must be materialized view.
I checked sys.objects, and object_a = 'U' (table), and object_b = 'V' (view), even though they both named with prefix VW_..., but naming conventions probably were ignored by developers.
June 24, 2019 at 8:16 pm
Ah, thanks Sue, when I placed my reply I did not notice that you already placed yours. Thanks, that's what I wanted.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply