Stuck on v9.2.17 because we cannot flatten schemas in future versions

Our team are still working in v9.2.17 as later versions introduced a new Schemas node into the object tree beneath the Databases node.

We have a lot of schemas but most of our work resides in just a few of them. Ideally we want to see Tables directly beneath the Database nodes and schemas as simply a part of the object name, with all tables in the selected database being listed regardless of their schema.

This is exactly how 9.2.17 worked and every time a new version comes out, I take a look to see if we can yet replicate that behaviour. Sadly I've always been forced to walk back to 9.2.17 in disappointment.

I'm growing aware though that there are likely now a large number of other improvements in more modern versions that we are missing out on and that we are no longer meaningfully able to suggest new features or participate in beta testing since we are stuck on such an ancient version of the software.

I've tried applying different database profiles to no avail. We use SQL Server 2008 R2 but I've tried the generic and Sybase profiles in the hope they would work differently. None of the other profiles seem to work correctly with SQL Server.

I've also tried the "Show Only Default Database/Schema" option but that doesn't do what we want as we need to expand and access multiple databases in particular and, to a lesser extent, access objects in multiple schemas. We simply don't want them to be grouped by schema when we expand the database.

I wanted to ask if there was yet any configuration setting we could apply to adjust the structure of the object tree to effectively flatten tables in all schemas into a single list of tables and ideally to remove the Schemas nodes altogether?

I use tables as an example but the same requirement applies to all our database objects. We'd want to see Tables, Views, Functions, Stored Procedures, etc as soon as we expand a database in the object tree.

Hi Hans,

Thanks for the swift reply. I've used filters before and they do let us restrict the list of schemas but, like you say, don't flatten all of our tables into a single list like the old versions of DBVis did.

Since v9.2.17 is the latest version that does what we want, I'll ask our staff to remain on that version instead.

Regards,

Vince

Hi Hans,

We integrate with many pieces of 3rd-party software. Some of these are legacy systems, unable to cope with schemas, and hence require all their tables in the default schema.

Other requirements elsewhere in our systems mean that some database objects have to be split by schema for specific systems. One implication of this is that our users each get their own default schema even though 99% of them are not actually used to create tables. 

We've looked into the impact of deleting these unused schemas but many need to remain because they are used to distinguish only certain types of object (e.g. stored procedures but not tables/views/UDFs). 

We are invested in this 3rd-party framework enough now that switching to another would be a multi-year programme of activity that we certainly are not in a position to undertake at present.

The end result is that we see a long list of schemas, most of which contain no tables or views. Accessing or manipulating data in tables and views is what we use DBVisualizer for most often. We tend to use SQL Server Management Studio for manipulating stored procedures and UDFs due to DBVisualizer not distinguishing the difference between an end-of-query delimiter (;) and a query batch separator (GO). I raised this previously as a separate issue.

When we browse tables we want to expand the databases node, select a database, expand the tables node then immediately see a list of all tables in that database. We care more about the object name than its schema so we have DBVisualizer configured to display object_name(schema) instead of the default schema.object.

Just one of our main database servers hosts 52 databases. The most common database we access on there has nearly 1400 tables and is accessed by over 2000 distinct users, well over half of which have their own database schemas. Very few of these schemas contain tables or views, which are the objects we most often need to access in DBVis.

As a result, once we expand the Tables node we see a Schemas node (which is a minor inconvenience as it's not any use to us to distinguish by schema at this point, only in code or external systems). When we expand the Schemas node, we see over 1100 schemas and have to scroll down pages to get to the dbo node where the majority of our tables are stored.

To add to the hassle, there are a small number of tables held under different schemas and accessing these also requires scrolling up and down pages and pages of unused schemas.

The older versions of DBVisualizer work perfectly for us. We can see the full list of Tables in any schema once we expand the Tables node and the schema name (which is of some but not much importance to us) is simply suffixed to the end (or, until we customised it, prefixed to the start) of the table name.

Later versions of DBVisualizer look like they offer a wealth of UI and feature enhancements but none of them are compelling enough to justify the hassle of navigating through a forest of mostly-empty schemas or having to hunt in multiple schemas to find a particular table name that we wish to access.

I fully understand that most of your clients may not have the degree of legacy 3rd-party integration that we do and therefore have the luxury of using schemas in the way Microsoft intended. We have neither the resource, funding nor authority to alter that situation in the short term. I can see that you have added features to DBVis that would be an improvement for the majority of these users but for us it represents a downgrade in efficiency of using DBVis due to our specific circumstances.

Supposedly, "You can please some of the people all of the time, you can please all of the people some of the time, but you can’t please all of the people all of the time". 

It seems we're some of the few you couldn't please by the promotion of schemas from part of the object name up to its own object tree node but I fully understand why this is a beneficial feature for your many clients who can and do use schemas the way they should be used in a modern database.

Regards,

Vince

Hi Hans,

We'll certainly balance up the pros and cons of this. 

In addition to having to click through an extra schema level in the object browser, it also means that we cannot see all our tables/views in one alphabetic list as they are still split across a few schemas.

We'd also need a procedure in place to ensure that we add any new schemas to the filter whenever a new one does get put into use.

The argument for staying on 9.2.17 would be "if it ain't broke, don't fix it" as it does fit all our needs at present. We'll certainly take another look at the latest release though and see if it adds enough new features which would be of use to us to offset the extra hassle of setting up, using and maintaining filters.

Thanks for all your suggestions and for taking the time to understand our issues clearly. 

Regards,

Vince