cross database column completion

bcunninbcunnin Posts: 4
edited September 1, 2008 11:56AM in SQL Prompt Previous Versions
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.

Comments

  • Thanks for your post.

    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.
    Chris
Sign In or Register to comment.