Virtual table SQL query

Hi Sofia,

Thanks for reaching out to support with your question. After looking into this briefly it looks like it might be tricky to get what you're looking for in the DB, "seeing the query of virtual tables" is easy to see in the report sql however -

Just want to confirm that's what you'd be looking to find here - in this case that production.product is being used by "vt report." Hoping I have it right?

I can check to see if the Audit Content reports have anything that includes this, as that's normally how these report tools are built. If there's nothing that identifies what you're looking for currently, I can make a request to look into the possibility of including it in the future. Otherwise, custom queries like this are beyond the scope of support technically, but there's the possibility of having a consultant look into assisting with this, is that something you'd like to explore?

Thanks,

Eric

Hi,

Thank you.

What we are trying to do is to find if there is any use of tables which we want to remove from our DB.

if the table is part of virtual table we can't identify it unless we start looking at views SQL.

Is there maybe any way to find the view SQL in the DB?

Thanks,

Sofia

Hi Sofia,

So far I've not found a way to get the info you're looking for, or any Audit Content report that includes this information, but I'll ask a couple of additional resources. If we can't find something, perhaps this is something you'd like to see included in a future version of the software?

Thanks,

Eric

I'm also in need of something similar to this. We need to find every report using a particular field within a table. I also saw the other YF link to a query that would give this information, but it appears that some of the tables it references are not available to us. I could go through every report's views sql code, but that could take forever and I'm sure we will have to do this again in the future.

Thanks,John

Hi Sofia,

Stepping in since Eric was out yesterday and today. You should be able to return Virtual Tables based on the query provided in the link... the issue is that when you're defining the WHERE conditions, most Virtual Tables don't have a ViewName assigned to it. You can confirm this by doing

SELECT * FROM ReportView WHERE ViewTypeCode = 'VIRTUAL';

If you change the .... 'and rv.viewname = 'New View'; section to 'and rv.viewname is null', it should return the appropriate results (Virtual Table values).

Regards,

Mike

Hi Sofia,

I just wanted to check in and see if there was anything else you needed here.

Regards,

Mike

Hi Sofia,

I'm going to go ahead and mark this one as Answered since I haven't heard back from you, but if you have further questions or concerns on this, if you respond, it will re-open the case and put it back in my queue and I'll be happy to help.

Regards,

Mike