Author |
Message |
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
|
|
I will be happy to provide an early private build to wet this new feature for usability and to receive your feedback as soon as this version becomes available. |
Feel free to drop me an email when you have something to test, I'll try to take a look at it and provide feedback.
|
|
Thu Apr 03, 2008 1:58 pm |
|
|
hitesh
Joined: 15 Oct 2007 Posts: 104
|
|
SQL Server: INFORMATION_SCHEMA |
|
HI,
I am using SQL Server 2005 Express Edition and SQL Assistance 3.1.22.
I have logged in as user 'sa'
What I have notice is that when I am typing a sql query (SELECT * FROM INFORMATION_SCHEMA.) on MASTER db, I get
intelisence popup list of SCHEMA objetcs like TABLES, COLUMNS etc (see SA1.jpg); but when i type the same SQL query on any other DB other than MASTER DB then it does not popups intelisence list on INFORMATION_SCHEMA. (see SA2.jpg)
Can you enable this in next release?
Thank You,
Hitesh
|
|
Fri Apr 04, 2008 8:18 am |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7903
|
|
|
|
This is not an SQL Assistant issue.
The database context query likely works in master database only and fails in all other databases. To verify that, paste the context query from SQL Assistant options to SQL Server Management Studio and execute it in different databases. I bet you will see some errors when you are not in master.
|
|
Fri Apr 04, 2008 10:40 am |
|
|
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
Auto-Join Suggestion |
|
Assuming:
Select *
From dbo.Table1 t
INNER JOIN dbo.
At this point, it would be great if you select a column instead of just a table it outputs the table name, alias, ON, the column names selected with the prefix, =, and if Table1 has a matching column name or a join, it outputs that as well. Examples in (computer) logic order:
It would append the following if Column1 was selected from Table2 and Table1 does not have column1 or a defined join:
Table2 t2 ON t.Column1 =
It would append the following if Column1 was selected from Table2 and if Table1 has both column1 and a defined join on the column, but different from column 1:
Table2 t2 ON t.Column1 = t.KeyRelationshipColumn
It would append the following if Column1 was selected from Table2 and Table1 does not have column1 but does have a defined join:
Table2 t2 ON t.Column1 = t.KeyRelationshipColumn
It would append the following if Column1 was selected from Table2 and Table1 also has column 1:
Table2 t2 ON t.Column1 = t2.Column1
|
|
Thu Apr 10, 2008 5:24 pm |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7903
|
|
|
|
Are you still running an old version?
|
|
Thu Apr 10, 2008 5:27 pm |
|
|
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
|
|
Are you still running an old version? |
3.1.24
|
|
Thu Apr 10, 2008 5:30 pm |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7903
|
|
|
|
Please disregard the previous post. From older posts it looks like you are using 3.1. Version 3.1 should be already doing all of that, even 3.0 could partially handle most of the described. When you select a "table" in the join, don't select a table on the second level, use the arrow right key to expand the level and then select the required join condition on the third level, the rest will be inserted automatically for you. It can handle both predefined joins (using foreign keys) and column name matching joins, these are displayed using a different color.
|
|
Thu Apr 10, 2008 5:32 pm |
|
|
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
|
|
Please disregard the previous post. From older posts it looks like you are using 3.1. Version 3.1 should be already doing all of that, even 3.0 could partially handle most of the described. When you select a "table" in the join, don't select a table on the second level, use the arrow right key to expand the level and then select the required join condition on the third level, the rest will be inserted automatically for you. It can handle both predefined joins (using foreign keys) and column name matching joins, these are displayed using a different color. |
This would also support occasions where joins are not defined within the same database or between databases. In those occasions, no join suggestions are provided, but could possibly be inferred.
|
|
Mon Apr 14, 2008 7:29 am |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7903
|
|
|
|
Sorry, we are not currently supporting cross-database joins. We are caching catalog data one database at a time and so join candidates from multiple databases are not available simultaneously.
|
|
Mon Apr 14, 2008 10:35 am |
|
|
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
|
|
Sorry, we are not currently supporting cross-database joins. We are caching catalog data one database at a time and so join candidates from multiple databases are not available simultaneously. |
If you were already at:
Select
From dbo.Table1 t
INNER JOIN Database2.dbo.
Wouldn't you already have both database tables and columns cached? Then if you selected a column name from Database2.dbo.Table2, you would already know a specific column from Database2 and you would only have to search the columns in Table1 of the local database.
|
|
Mon Apr 14, 2008 11:20 am |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7903
|
|
|
|
No. Only the current database is cached. There are few exceptions when we go beyond the current database and pull the catalog data directly, for example, if you refer to a table in other database in the SELECT or WHERE clauses, but it doesn't work the same way for joins in SQL Server. For performance reasons, suggestions for JOINs come from the internal cache.
BTW, In Sybase, cross-database ref. constraints can be defined in tables in multiple databases and this info is stored in catalog tables just like same-database ref. constraints and available in the JOIN popups.
|
|
Mon Apr 14, 2008 12:01 pm |
|
|
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
|
|
No. Only the current database is cached. There are few exceptions when we go beyond the current database and pull the catalog data directly, for example, if you refer to a table in other database in the SELECT or WHERE clauses, but it doesn't work the same way for joins in SQL Server. For performance reasons, suggestions for JOINs come from the internal cache. |
Using the example above, you would only have to look at the local database for a column of the same name as the specific column chosen by the user.
I think we have a disconnect. I'm not saying you should popup join suggestions. The suggestion is centered on if a user goes down to a column name from a list of tables in a join clause (outside of the join suggestions), you use the column name.
Currently Column1 under Table2 from here (in the table list):
Select * from dbo.Table1 t INNER JOIN dbo.
Results in:
Select * from dbo.Table1 t INNER JOIN dbo.Table2.Column1
I was suggesting outputting something like this instead:
Select * from dbo.Table1 t INNER JOIN dbo.Table2 ON t2.Column1
This would allow proper use of the user selection. Then at this point, you could extend it using some of the suggestions I mentioned earlier.
|
|
Mon Apr 14, 2008 1:47 pm |
|
|
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7903
|
|
|
|
Perhaps, I'm missing something. The provided example is not using a cross-database join. Are you suggesting, expanding a column selection in a join list to a JOIN [table] [alias] ON [alias].[selected column] text? In other words, if a table-level is expanded in the object list displayed after join and a column is selected., then convert this into [table] ON [column] rather than [table].[column]. If I get it right, it can be surely done.
|
|
Tue Apr 15, 2008 6:56 am |
|
|
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
|
|
|
|
In other words, if a table-level is expanded in the object list displayed after join and a column is selected., then convert this into [table] ON [column] rather than [table].[column]. If I get it right, it can be surely done. |
Yes. Exactly. Then I was taking that an additional step if possible. Since you have the other tables in the from clause you could see if that chosen column name has a defined join to the other tables or the same column name to automatically output the "= t2.Column" portion of the ON. However, one possible issue is if there is a lot of tables in the from clause. Searching them might be a hit. A solution to that would be to only search the previous table in the From clause.
|
|
Tue Apr 15, 2008 7:22 am |
|
|
judahr
Joined: 09 Mar 2007 Posts: 319 Country: United States |
|
Selectable Joins Header |
|
v. 3.5.11
When you have
|
|
Select * from dbo.Table t INNER JOIN |
When possible, it pops open with a table with --joins as a suffix. I wish I could select that table as an option. It would be great for self joins or if you need the table twice for whatever reason.
|
|
Thu May 08, 2008 11:46 am |
|
|
|