Table named SYS vs sys.

Bamajohn

How can I tell SQL Prompt to ignore a table named SYS in our vendor-provided database and instead prefer the "sys." that prefixes "server_principals" and "database_principals" and all those items? It's driving me crazy not letting me type "sys." without it thinking I mean the table.

Aaron L

Hi Bamajohn,

I'm just looking into this now and I'm wondering if you could provide some examples of where this is causing you a problem? I just gave it a try with something simple like "SELECT * FROM sys." and it seems Prompt initially uses the sys table (as it should) but after the dot is entered it resolves it to the schema and suggests all the sys views/tables.

Is the sys table from the vendor provided database in its own schema? We have a uservoice request to extend our connection filtering to cover excluding schemas too so I'm wondering if that would help if it were implemented?

Thanks,
Aaron.

Bamajohn

No, the vendor table is in the dbo schema. My trouble is I type "Select * from sys." and I get "Select * from SYS as s." - Prompt is assuming I want the table SYS and is going ahead with its alias.

Aaron L

Thanks for the extra info - I can now recreate it here. You're correct that it's completing the table instead of the schema as tables are first in the suggestions ordering, which is normally the desired behaviour.

A possible work around is to enable "List system objects" on the behavior page of the options. If you're using the sys schema a lot this'll suggest the objects from sys without having to qualify with it.

I think we could also potentially disable the "as s" alias generation if you're completing a table with a dot as I don't think it's ever valid sql to put a dot after an alias declaration. I'll see if we can generate you a new build with this in.

Thanks,
Aaron.

Aaron L

Hi Bamajohn,

I've just uploaded a new beta build which won't auto-complete the alias if you use dot as the completion key. Since your SYS table is in the dbo schema you can also turn off the "dbo." qualification being inserted automatically through Options->Qualification->Qualify object names with owner names.

Thanks,
Aaron.