Home

Db schema prefixing not working for all tables

Hi,

I have a database in which I have the dbo schema and another schema (x) with its own tables etc.

There are tables under dbo and x with the same names. Some of these tables are available to autocomplete with X_Conflictingname but not all of them.

So I'm looking at a table Sometable under dbo schema but x.Sometable is not visible in the connection tree on the left and I can't access it via X_Sometable from my code either.

The tables under x that does not have conflicting names are available to my linq queries.

I'm a bit confused here. Why some tables are prefixed but others are not? How do I access the missing tables under x that seems to have been overriden with the ones from dbo schema?

Comments

  • When a conflict arises, it should prefix the conflicted table name with the schema name. For example, in the AdventureWorks database, the Employee table exists in both the HumanResources and Person schemas, so it presents as HumanResources_Employees and Person_Employees. All other tables have no prefix because there is no conflict. Does this answer your question?
  • edited April 2021

    I have a few problems with how prefixing is done when 2 similar table names occur in different schemas
    Casing hides a conflicting table.
    It appears that is confused by 2 similar table names (diff schema) such as:
    BadTestData.Company_BKP and
    rein.Company_Bkp

    I see only one Company_Bkp. Fixing the casing results in two tables prefixed as expected.

    Prefixing is dependent on the existing table names, and thus changes as tables are added, In my case the test tables are not always present. I would prefer an option that prefixed all of the names on the connection, even if not needed at the moment.

    Prefixing is inconsistent with how tables are named in Visual Studio, and in SQL Server; requiring a translation (in my already overtaxed brain), sometimes. I cannot just simply copy & paste code from one place to another.

    When a table is prefixed, the new name causes the table to show up in a different place in the list. It is easy to incorrectly assume the table does not exist or cannot be seen by LINQPad, when it is just me, the user, that does not remember to look elsewhere. Given names like a1Table, a2Table, a3Table. When a2Table is renamed SomeSchema_a2Table it may appear so far down the list as to not be visible without scrolling.

    BTW I am quite sure #4 the sort order this does not explain point #1, as that schema has few tables. As soon as I rename the table in SSMS and refresh in LINQPad, it appears as expected.

    Possible Enhancements
    I know you are a really smart guy and that you thought about this, probably a lot. I also know you cannot change the existing functionality. But what about a user or per connection option to handle this differently, such as always prefixing or separating schemas into different connections to avoid conflicts?

    Thanks for your consideration.

Sign In or Register to comment.