You can translate the question and the replies:

Query for eliminating non-dependent/unnecessary views

Hi, I have migrated views to QA environment and there are some views that are not dependent of the final Interface view, to retrieve the dependent views, I use this query : SELECT DISTINCT view_database_name, view_name, view_type, private_view, dependency_database_name, dependency_name, dependency_database_name || '.' || dependency_name AS name, dependency_type FROM view_dependencies() WHERE view_name = '#########' AND private_view = false AND substr(dependency_name FROM 1 FOR 1) <> '.' AND substr(dependency_name FROM 1 FOR 3) <> 'ds_' order by Dependency_name; My question is, I want to find the list of views that are not dependent or related so I can get rid of them and clean QA environment. Is there a query to do that directly or can I use 'MINUS' from the above query to do that? Can you help? Regards!
05-08-2021 13:36:39 -0400

3 Answers

Hi, Yes, you can use MINUS operation. I would accomplish this by using VIEW_DEPENDENCIES() and GET_VIEWS(). And since the VIEW_DEPENDENCIES procedure returns the view_name and dependency_name, you can get all the view_name which are not in the dependency_name column or not dependent views. Please find the syntax as below, `SELECT * FROM VIEW_DEPENDENCIES() AS A RIGHT OUTER JOIN GET_VIEWS() AS B ON A.dependency_name =` Hope this helps!
Denodo Team
06-08-2021 13:38:34 -0400
Hi, I have tried this code, but it did not produce any result, could you please direct me a good way to find non-dependent views? Thanks!
13-08-2021 11:26:54 -0400
Hi, Please try running the query as an administrator to get all the information since you don’t have appropriate access to the missing elements. As a regular user, the behavior of the stored procedures are more restricted. Hope this helps!
Denodo Team
20-08-2021 17:34:22 -0400
You must sign in to add an answer. If you do not have an account, you can register here