I’ve been working lately on migrating a MySQL database to SQL Server. As part of this process, once the database has been migrated, an update of the program using the database is run, which makes some changes to the structure of the database. I designed a nice little SSIS package to migrate the data from MySQL to SQL Server. Than runs without problems (and utilizes this nice ADO/.NET driver for MySQL). We are currently seeing problems during the upgrade process.

The upgrade makes some new tables and modifies existing tables in the database once it is in SQL Server. Because we are hitting errors, we are reloading the database and re-running the upgrade process multiple times a day as we troubleshoot. We ran into a situation where the upgrade failed after creating some new tables. In order to re-run the upgrade process, I needed to delete those new tables. SSMS has a nice filter option that can filter which tables (or other database items) are displayed. I would like the filter to show only the new tables created by the failed upgrade, which were created about 1.5 hours after the other tables were created.

Here’s a shot of the table properties of one of the old tables.

I want to show all tables created on 7/28/10 after 4:29 PM, which would be the ones the upgrade process created. When I tried to make such a filter, this is what I got:

Hmm.. I have to select a date from the calendar and I cannot type a date in. Which means I cannot enter a time portion. Which means my minimum filter granularity is 1 day. If anyone from Microsoft is reading this, I’d like to see this changed to allow times as well. I understand the calendar is used to simplify things for the user, but I think you can still use the calendar for date picking, but then display a time portion of midnight (which is really what a date only means) or 11:59:59 PM, depending on which side of the day you want to default to. That way, you still get the ease of use of the calendar but if the user needs to, he or she can change the time portion as well.

(I know I can write some T-SQL to query sys.tables, get the created datetime, and delete the appropriate tables, but I was using the GUI and thought this would be quicker.)

Another pet peeve of mine regarding SSMS involves scripting. The default behavior, when you right click a table and choose Script Table As…, is that the resulting script will not include any indexes other than the one that defines the primary key. Turns out, this is an option setting that you can change. In SSMS, go to Tools –> Options and expand the SQL Server Object Explorer node, then select Scripting. Set the Script Indexes property to True.