cross database column completion
bcunnin
Posts: 4
Hello, I have the following situation. Please let me know if I am doing something wrong. I am using SQL Prompt 3.9.0.43. I have the following options settings:
under "Listed Candidates": enable cross-database and linked server support = checked
under "Join Conditions": column names = checked, data types = checked, part of a multiple column foreign key= checked
under "candidate types and filters" variables, scripted objects and paramenters = checked, system objects = UNchecked, list all columns in database after select = UNchecked, filter candidate list by owner/schema prefix = checked, enable case-sensitive filtering of candidates = checked
Under "Inserted Candidates", under "Formatting" I have qualify object names = checked, qualify column names = checked, when insertng multiple columns, use a new line for each = checked, surround identifiers with square brackets = UNchecked, for built in functions = checked, insert new line at current indent level = checked
I am editing a query that has already been created. The query has two tables. The column names across both tables are unique. One table is in the current database, the other is in a separate database.
I have something like this:
select
dbo.TableInCurrentDatabase.aColumn
, dbo.TableInCurrentDatabase.bColumn
, otherDatabase.dbo.TableInOtherDatabase.cColumn
, dbo.TableInCurrentDatabase.dColumn
from
dbo.TableInCurrentDatabase
inner join otherDatabase.dbo.TableInOtherDatabase
on dbo.TableInCurrentDatabase.yColumn = otherDatabase.dbo.TableInOtherDatabase.zColumn
Now, I want to add another column (pColumn) to the select list and the column is in the table that is in otherDatabase, but since the column names are all unique, i just start typing it's name. As soon as I type the "p", I get prompted with pColumn at the top of the list, which is great, but when i hit Tab to insert it, what gets inserted is "dbo.TableInOtherDatabase.pColumn" and not "otherDatabase.dbo.TableInOtherDatabase.pColumn" which is what I think should get inserted, because when I try to run what SQL Prompt inserted, I get a syntax error, just like you'd expect. Please let me know what I'm doing wrong. Thank you.
under "Listed Candidates": enable cross-database and linked server support = checked
under "Join Conditions": column names = checked, data types = checked, part of a multiple column foreign key= checked
under "candidate types and filters" variables, scripted objects and paramenters = checked, system objects = UNchecked, list all columns in database after select = UNchecked, filter candidate list by owner/schema prefix = checked, enable case-sensitive filtering of candidates = checked
Under "Inserted Candidates", under "Formatting" I have qualify object names = checked, qualify column names = checked, when insertng multiple columns, use a new line for each = checked, surround identifiers with square brackets = UNchecked, for built in functions = checked, insert new line at current indent level = checked
I am editing a query that has already been created. The query has two tables. The column names across both tables are unique. One table is in the current database, the other is in a separate database.
I have something like this:
select
dbo.TableInCurrentDatabase.aColumn
, dbo.TableInCurrentDatabase.bColumn
, otherDatabase.dbo.TableInOtherDatabase.cColumn
, dbo.TableInCurrentDatabase.dColumn
from
dbo.TableInCurrentDatabase
inner join otherDatabase.dbo.TableInOtherDatabase
on dbo.TableInCurrentDatabase.yColumn = otherDatabase.dbo.TableInOtherDatabase.zColumn
Now, I want to add another column (pColumn) to the select list and the column is in the table that is in otherDatabase, but since the column names are all unique, i just start typing it's name. As soon as I type the "p", I get prompted with pColumn at the top of the list, which is great, but when i hit Tab to insert it, what gets inserted is "dbo.TableInOtherDatabase.pColumn" and not "otherDatabase.dbo.TableInOtherDatabase.pColumn" which is what I think should get inserted, because when I try to run what SQL Prompt inserted, I get a syntax error, just like you'd expect. Please let me know what I'm doing wrong. Thank you.
Comments
You might find this easier if you use aliases in the query. You can then define the 'otherDatabase' as something like otherDatabase.dbo.TableInOtherDatabase as _tiod, then whenever you refer to it you can use _tiod.pColumn.
It is also possible to enable SQL Prompt to automatically assign an alias to a table, see: SQL Prompt > Options > Auto Insert > Aliases.
If you have assigned aliases to the tables, then your query might look something like:
select
ticd.aColumn
, ticd.bColumn
, _tiod.cColumn
, ticd.dColumn
from
dbo.TableInCurrentDatabase as ticd
inner join otherDatabase.dbo.TableInOtherDatabase as _tiod
on ticd.yColumn = _tiod.zColumn
I hope this is helpful.