DB2 Views

10 pts.
Tags:
IBM DB2
How to find the number of views for a DB2 table?

Answer Wiki

Thanks. We'll let you know when a new response is added.

A query against the DB2 catalog will answer your question.

To find the number of views, something like this will work:
SELECT COUNT(*) from SYSIBM.SYSVIEWDEP
WHERE BTYPE = ‘V’ — this means VIEW
AND BNAME = ‘name of table in question’

To find the names of the views on a given table, something like this:
SELECT DCREATOR, DNAME <– the CREATOR & NAME of the view
FROM SYSIBM.SYSVIEWDEP
WHERE BNAME = ‘a given table’
AND BCREATOR = ‘owner of the given table’
ORDER BY …

===

part 2 – It was pointed out that I had mistyped the columns in the SELECT list in the second query above. These have been corrected.

meandyou (Steve)

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • CHINWGUY
    The answer is partial correct. The query SELECT COUNT(*) from SYSIBM.SYSVIEWDEP WHERE BTYPE = 'V' -- this means VIEW AND BNAME = 'tbl_name' -- the table name desired AND BSCHEMA = 'tbl_creator' -- creator of the table named desired returns all the virews that are directly dependent on the view in question. A recursive query is needed against SYSVIEWDEP to get the count of all the views that are dependent upon the tables in question. This is where it could get tricky if the level of recursion is infinite. This answer to this question is better answered under the question on recursion that here. I will write a complete answer there when I write the examples and queries against them
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following