Create Table form is modal

I've just noticed that the Create Table dialog is a modal form, preventing browsing of other objects whilst creating a new table. This isn't ideal if you want to refer to fields in other tables to determine required field sizes, types, naming conventions, etc. I may be missing something but I can't see a good reason to prevent browsing of existing objects whilst designing a new one.

That dialog could also do with being more keyboard-friendly. If I press ENTER on the last column of the row, I'd expect to to make a new row, not jump back to the first column. I know I can use CTRL-I to add a new row but that's far less intuitive. If I enter VARCHAR(255) as a data type, I'd expect it to parse it into the Data Type and Size columns, not generate an error message. This one is a duplicate issue I've mentioned before but I mention it again as I still find it gets me nearly every time. If I type VARCHAR into the Data Type column and then press TAB I'd expect it to jump to the Size column. At present I have to press ENTER before I can TAB to the next column. The default schema for my connection should be selected when the dialog is first opened. At the moment I have to select the blank schema and scroll down quite a way to find dbo. The Execute button does what it says on the tin but it would be handy to have an "Export to SQL" button too so that I can create the same table on several databases (e.g. live, test and development versions) or customise the SQL by hand before the table is created.

Hi, We have discussed making the dialog non-modal and will probably do this, or something else that describes the problem you describe, in a future release along with other usability changes. I have added you vote for it. Best Regards, Hans

Hi, > If I press ENTER on the last column of the row, I'd expect to to make a new row, not jump back to the first column. I know I can use CTRL-I to add a new row but that's far less intuitive. ENTER is used for so many things in grid editing that it is hard to also bind it to "Insert New Row", but you can bind something like Ctrl-Enter to this operation in Tool Properties->Key Bindings. > If I enter VARCHAR(255) as a data type, I'd expect it to parse it into the Data Type and Size columns, not generate an error message. This one is a duplicate issue I've mentioned before but I mention it again as I still find it gets me nearly every time. This is harder than it may seem, because the data type list is retrieved from the database (so we don't have full control over what's in it) and it is read-only, so you can't type "VARCHAR(255)"; making it editable would likely cause other problems. Is it really so much harder to tab to the Size field and add the size there? > If I type VARCHAR into the Data Type column and then press TAB I'd expect it to jump to the Size column. At present I have to press ENTER before I can TAB to the next column. I'll open a ticket on allowing TAB to be used to select an entry here, since that would make it consistent with other lists, e.g. Auto Completion. > The default schema for my connection should be selected when the dialog is first opened. At the moment I have to select the blank schema and scroll down quite a way to find dbo. It should already work that way. Which database and driver are you using? > The Execute button does what it says on the tin but it would be handy to have an "Export to SQL" button too so that I can create the same table on several databases (e.g. live, test and development versions) or customise the SQL by hand before the table is created. There are Copy to Editor choices in the SQL Preview area right-click menu. Is that sufficient? Best Regards, Hans

> We have discussed making the dialog non-modal and will probably do this, or something else that describes the problem you describe, in a future release along with other usability changes. I have added you vote for it. Thanks - that'll save me having to use Management Studio or a 2nd copy of DBVis to examine the structure of existing objects whilst designing new ones. > > If I press ENTER on the last column of the row, I'd expect to to make a new row, not jump back to the first column. I know I can use CTRL-I to add a new row but that's far less intuitive. > > ENTER is used for so many things in grid editing that it is hard to also bind it to "Insert New Row", but you can bind something like Ctrl-Enter to this operation in Tool Properties->Key Bindings. That sounds a good plan - I see it's already also bound to the Insert key, which does seem more intuitive. > > If I enter VARCHAR(255) as a data type, I'd expect it to parse it into the Data Type and Size columns > > This is harder than it may seem, because the data type list is retrieved from the database (so we don't have full control over what's in it) and it is read-only, so you can't type "VARCHAR(255)"; making it editable would likely cause other problems. Is it really so much harder to tab to the Size field and add the size there? I can understand the difficulty with this one and I agree that it wouldn't be such a problem once the autocomplete bug is sorted. At present I must type VARCHAR255, which isn't intuitive. > > If I type VARCHAR into the Data Type column and then press TAB I'd expect it to jump to the Size column. At present I have to press ENTER before I can TAB to the next column. > > I'll open a ticket on allowing TAB to be used to select an entry here, since that would make it consistent with other lists, e.g. Auto Completion. Thanks - this would improve the feel of the dialog greatly. > > The default schema for my connection should be selected when the dialog is first opened. At the moment I have to select the blank schema and scroll down quite a way to find dbo. > > It should already work that way. Which database and driver are you using? I'm accessing Sql Server 2008 via the jTDS driver using windows authentication. I'm configured to use dbo as the default schema. I attach the blank schema dialog I see when I first open the dialog. > > The Execute button does what it says on the tin but it would be handy to have an "Export to SQL" button too so that I can create the same table on several databases (e.g. live, test and development versions) or customise the SQL by hand before the table is created. > > There are Copy to Editor choices in the SQL Preview area right-click menu. Is that sufficient? Thanks - that does just what I need. I didn't think to check the right-click menu.

Hi, Regarding the default schema not being set in the Schema list, please enable debugging just before you open the Create Table dialog and post/send us the output. http://www.dbvis.com/products/dbvis/doc/faq/#4.14 Another thing you can try is to run this SELECT statement and tell us what you get: SELECT P.DEFAULT_SCHEMA_NAME FROM SYS.DATABASE_PRINCIPALS P, SYS.SQL_LOGINS L WHERE L.PRINCIPAL_ID = P.PRINCIPAL_ID AND L.NAME = ''" where is replaced with your login account name. Best Regards, Hans

The problem it seems is that the query only works for SQL Server logins, not domain authentication. The query you posted fails to return any rows: +SELECT+ +P.DEFAULT_SCHEMA_NAME+ +FROM+ +SYS.DATABASE_PRINCIPALS P, SYS.SQL_LOGINS L+ +WHERE+ +L.PRINCIPAL_ID = P.PRINCIPAL_ID AND L.NAME = ''+ However, this query returns one row with the correct default schema: +SELECT DEFAULT_SCHEMA_NAME+ +FROM sys.database_principals+ +WHERE NAME = ''+ I have a debug log as well if you still need it but this looks like the smoking gun to me. BTW, confirmed still broken in version 8.0.

Hi, Thanks. Which version of the database are you using? Best Regards, Hans

It's SQL Server 2008 set up to allow both SQL logins and windows authentication. Normally we log in using windows logins but soem of our legacy applications still require SQL logins. A quick fix to work with both would seem to be to look at P.NAME instead of L.NAME and remove the join to the SQL_LOGINS table entirely.

Hi, I admit I don't have a lot of experience with this, so please correct me if I'm wrong. In my test database, I also use SQL logins. They are listed in the SQL_LOGINS view with a PRINCIPAL_ID, which is then mapped to a default schema in DATABASE_PRINCIPALS. Hence, the current query works fine in my case. If I just change to the query you suggest, the configuration I use would fail, because the login name is not among the names listed in DATABASE_PRINCIPALS; that table must be joined via the PRINCIPAL_ID from DATABASE_PRINCIPALS, as it is today. From reading a bit about this, it seems like SYS_LOGINS should always have an entry for all types of logins, e.g. for both SQL Logins and Windows Logins. Are you saying that is not the case in your configuration? Best Regards, Hans

Hi Hans, I can confirm that DATABASE_PRINCIPALS contains both sql logins and windows logins in my case. In fact it +must+ also be there in your case too if the full query works as the original query performs an INNER JOIN (although in pre-ansi join syntax) to the DATABASE_PRINCIPALS table. Hence if no matching row existed in DATABASE_PRINCIPALS, no row would be returned in the resultset even if one existed in SQL_LOGINS. To return the data from SQL_LOGINS even if DATABASE_PRINCIPALS didn't exist the WHERE clause would have to perform an OUTER JOIN (using \*= or =\* in the old syntax, which no longer works in SQL Server). Even then though, without a row in DATABASE_PRINCIPALS, the column DEFAULT_SCHEMA_NAME in the resultset would end up being NULL. I can confirm a row also exists for both types of login in the SYSLOGINS table but as that table doesn't contain the default schema it's not going to solve the original problem.

Hi, Thanks for the additional info. I will have a look at this in more detail next week. I'm on the road at the moment and don't have reliable access to my SQL Server test database. Best Regards, Hans

Hi, In my config, DATABASE_PRINCIPALS does not contain the SQL user I use to login in the NAME column. Instead, SQL_LOGINS maps the user name (in the NAME column) to a principal ID (in the PRINCIPAL_ID column), which is then mapped to a principal name with a default schema in the DATABASE_PRINCIPALS table via the principal ID. So you're right that the DATABASE_PRINCIPALS contains the SQL logins even in my case, but only the PRINCIPAL_ID for it, not the NAME. However, I found a standard function that can hopefully be used to solve the problem for both our configurations, namely User_Name(). As far as I can tell, it always returns a value that matches a principal name in DATABASE_PRINCIPALS. We should therefore be able to replace the current query with this one: SELECT DEFAULT_SCHEMA_NAME FROM SYS.DATABASE_PRINCIPALS WHERE NAME = User_Name(); Please verify that it gives the correct result for your configuration. And if anyone sees a problem with it, please let us know. Best Regards, Hans

Hi Hans, I can confirm that solution also works fine with my SQL Server 2008 setup. Thanks for taking the time to find it.

Hi, Thanks for confirming. The new query will be used in the next maintenance release. Best Regards, Hans