Feature Request: recursive display of "Used by"

57 views
Skip to first unread message

Franz Mayer

unread,
Oct 18, 2021, 4:31:29 AM10/18/21
to SQL Workbench/J - DBMS independent SQL tool
Hi,

would it be possible to get recursive tree of dependent DB objects in DbTree panel?

I only get first dependent level and need to click "Find in tree". This is quite cumbersome - it would be much better to have a recursive tree.

Thank you in advance,
Franz

Thomas Kellerer

unread,
Oct 18, 2021, 9:34:21 AM10/18/21
to sql-wo...@googlegroups.com
Hello,

this is currently only available in the DbExplorer, where there is a check box to toggle the behaviour.

Doing this by default is quite expensive (depending on the DBMS being used), that's why the DbTree only retrieves the first level.

I'm not sure how the UI to offer such a choice in the tree could look like.

Regards
Thomas

Matthias Melzner

unread,
Oct 29, 2021, 3:30:13 AM10/29/21
to SQL Workbench/J - DBMS independent SQL tool
Hi,

I added the dependency nodes for the procedures. I already tested it with a MS SQL Server without any issues. I also needed to add the type "PROCEDURE" because the type "procedures" didnt't work for me.

In the attachemnt you can find the patch file with my changes.

Matthias
TreeLoader.patch

Thomas Kellerer

unread,
Oct 29, 2021, 11:38:56 AM10/29/21
to sql-wo...@googlegroups.com

Hmm, the dependency nodes below a procedure are already added (see ProcedureLoader.loadProcedures()) so I am not sure what exactly this achieves.

The "Uses" and "Used by" nodes already support recursive display:



I think Franz was referring to the "References" and "Referenced by" nodes which currently do not support a recursive display like the above.

Regards
Thomas

--
You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/aa182789-2370-452b-864c-2257dbff0d1en%40googlegroups.com.

Thomas Kellerer

unread,
Oct 31, 2021, 1:23:00 PM10/31/21
to SQL Workbench/J - DBMS independent SQL tool
I just realized that if you are talking about FK references, this is actually quite easy to add, so that each "referenced" node becomes a complete functional table node where you can expand columns, indexes and FK references again.

The "Used By" and "Uses" parts already allow that.

Matthias Melzner

unread,
Nov 3, 2021, 5:21:06 AM11/3/21
to SQL Workbench/J - DBMS independent SQL tool
 When I only use TreeLoader.loadProcedures() on the node "stp_CalculateCostAccountModel" the node appears to be empty in the DbTree.
Screenshot 2021-11-03 094958.png

With the addition of TreeLoader.addDependencyNodes() the  node now has the children "Uses" and "Used By" consisting out of table nodes and other procedure nodes.
Screenshot 2021-11-03 094858.png

I also needed to adjust the type name to "PROCEDURE" although I don't know if this is specific to the DB I used.
I hope this clears up the purpose of my added code. 

Thomas Kellerer

unread,
Nov 3, 2021, 9:52:54 AM11/3/21
to sql-wo...@googlegroups.com
> When I only use TreeLoader.loadProcedures() on the node "stp_CalculateCostAccountModel" the node appears to be empty in the DbTree.

Yes of course, because loadProcedures() loads procedures for a specific catalog (=database) and schema.
It's not intended to load dependencies between objects

> With the addition of TreeLoader.addDependencyNodes() the  node now has the children "Uses" and "Used By" consisting out of table nodes and other procedure nodes.

It will show objects that use or are used by that specific procedure - limited by the capabilities of the specific DependencyReader.

If you are missing objects in those nodes, you should extend the DependencyReader for your database e.g. SqlServerDependencyReader

> I also needed to adjust the type name to "PROCEDURE" although I don't know if this is specific to the DB I used.
> I hope this clears up the purpose of my added code.

No, not really. The type name of a single node that represents a procedure is implicitly defined by the DbObject that is attached to that node. See ObjectTreeNode.getType(). If the node represents (=contains) a ProcedureDefinition the type will be "PROCEDURE"


Thomas

Matthias Melzner

unread,
Nov 8, 2021, 9:10:03 AM11/8/21
to SQL Workbench/J - DBMS independent SQL tool
Would you consider adding this feature into the project?

I believe with a feature like this looking through a DB and understanding the connections between the different elements is much easier.
It also helps following the DbTree further because you can see the nodes "Used By" and "Uses" of a procedure.


Regards,

Matthias

Thomas Kellerer

unread,
Nov 8, 2021, 11:30:19 AM11/8/21
to sql-wo...@googlegroups.com
Sorry, I don't understand which feature you mean.

"Used By" and "Uses" already work for stored procedures. The exact kind of dependendencies that are displayed there depend on the DBMS being used.

If your DBMS supports more than what is currently implemented, then the corresponding DependencyReader needs to be changed.

Franz Mayer

unread,
Nov 9, 2021, 5:11:45 AM11/9/21
to SQL Workbench/J - DBMS independent SQL tool
Hi Thomas,

maybe the attached Screenshot make it clear, what Matthias has implemented.

We only tested that for MS SQL Server, so I don't know if the recursive procedure call in other DBMS is working.

Kind regards,
Franz
2021-11-09_compare_Wb_Tree.png

Thomas Kellerer

unread,
Nov 9, 2021, 4:35:46 PM11/9/21
to sql-wo...@googlegroups.com
Ah! Now I get it ;)

Yes, that makes, sense. But it can be done much easier. The dependency nodes just need to be added in the loadDependencies() method.

Thanks for clearing this up!

Regards
Thomas
> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com <mailto:sql-workbenc...@googlegroups.com>.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/432cc56d-2257-40ca-b0eb-10e12715935dn%40googlegroups.com <https://groups.google.com/d/msgid/sql-workbench/432cc56d-2257-40ca-b0eb-10e12715935dn%40googlegroups.com?utm_medium=email&utm_source=footer>.
Reply all
Reply to author
Forward
0 new messages