 |
SoftTree Technologies
Technical Support Forums
|
|
Author |
Message |
MarcinH
Joined: 18 May 2019 Posts: 7 Country: Poland |
|
MS SQL Server: no automatic aliases after a table name |
|
SQL Assitant v10.1.278
SSMS v17.9.1
MS SQL Server 2016 SP1, 2012
After FROM clause when I type few characters and choose a table name from the list, SQL Assistant often does not automatically add an alias to that name.
Sometimes it works, sometimes it doesn't.
I think it's a problem from SQL Assistant 10.1.x version.
Thanks
Marcin
|
|
Sat May 18, 2019 12:20 pm |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
Please provide example of table for which is doesn't generate an alias automatically. Please also describe the context in which the SQL Assistant is used, the context is very important too.
If the statement is like UPDATE, ALTER, etc... (or somehow it "thinks" the context is not a SELECT query), it won't add an alias automatically.
|
|
Sat May 18, 2019 9:13 pm |
|
 |
MarcinH
Joined: 18 May 2019 Posts: 7 Country: Poland |
|
|
|
It's just a simple select statement like: SELECT * FROM table.
SQL Assistant options:
Always Fully Qualify Object Name = With schema name
Name Matching Method = Name Contains Characters from Key String, Order by Best Match
Auto Add Aliases = Yes (without AS keyword)
Aliases Character Case = Lower case
Custom Aliases = Regular names
I think I know the pattern:
For the test let's assume the table is:
CREATE TABLE dbo.MyTable (col1 INT PRIMARY KEY, col2 VARCHAR(100));
1. I start typing: SELECT * FROM[space]
After the space after the FROM, the object list is automatically shown and typing just a few characters of MyTable (for example mt) I can choose the table name and the alias mt is automatically added:
SELECT * FROM dbo.MyTable mt.
Success!
2. I start typing: SELECT * FROM[space]
After the space after the FROM, the object list is automatically shown but I hit Escape. Then I'm typing just a few characters of MyTable (for example mt). I hit CTRL+SPACE to show the matching object list, then I choose MyTable. The table name is added but the alias is not added.
Failed!
3. The same scenario as 1. But when the alias mt is automatically added I delete the alias and the table name to the space (but not the space) after the FROM. I start typing, I hit CTRL+space. The table name is added but the alias is not added.
Failed!
4. I start typing: SELECT * FROM[space]
After the space after the FROM, the object list is automatically shown but I hit Escape. Then I'm typing exact schema name (dbo.), the object list is shown. Then I hit Escape. Typing a few characters of MyTable (for example mt). I hit CTRL+SPACE to show the matching object list, then I choose MyTable. The table name is added AND the alias mt is automatically added.
Success!
Conclusion: alias is only automatically added when the database object list is shown automatically or when I add the schema name.
Alias is not added when the table was chosen by CTRL+SPACE
|
|
Mon May 20, 2019 9:25 am |
|
 |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7948
|
|
|
|
Thank you for the provided examples. I discussed them internally and I believe both behaviors behaviors 2 and 3 are by design. The forced popup (Ctrl+Space) doesn't work as a continuation of the previous SQL statement when you type SELECT * FROM ... It's in place popup enabling you to paste object name rater then type it.
To see what I mean, start with an empty editor, press Ctrl+Space. similarly type
SELECT * FROM press Escape to dismiss the popup for SELECT * FROM ...
press Enter key couple of times
Enter /* some comment here */ then press Ctrl+Space and choose something. ..
In other words Ctrl+Space is not the same as an automatic sentence completing popup.
Just in case, there is another key Shift+Enter that can be used to select a name in an automatic popup and insert it as is without expanding columns and without adding an alias.
|
|
Tue May 21, 2019 8:55 am |
|
 |
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
|
|