At work, we’re in the process of trying out some new development projects in Java. And I guess we’re just masochistic, since we decided to write this new project against MSSQL instead of our usual MySQL. Anyways, everything’s coming together for the most part, except that few of the GUI database tools seem to be able to find our schema. Instead, it just seems to list the database users. We noticed this in Netbeans, Eclipse, and SquirrelSQL.
Yesterday, I started tracking it all down. Turns out, Microsoft introduced the concept of an actual schema in SQL Server 2005. Prior to that, tables were segmented by their owner. Consequently, the available JDBC drivers (both jTDS and Microsoft’s) implementations of DatabaseMetaData.getSchemas() actually return the list of database users.
Since I can’t do anything about Microsoft’s driver, I downloaded the source for jTDS and started poking around. In net.sourceforge.jtds.jdbc.JtdsDatabaseMetaData, around line 1589, you’ll find the following query:
SELECT name AS TABLE_SCHEM, NULL as TABLE_CATALOG FROM dbo.sysusers
After digging up a copy of the 1.5 JDK, I changed this to a query based on the information schema and recompiled. Then I fired up SquirrelSQL, and, bam. All my schemas were listed. Feeling quite proud of myself, I then discovered that someone had already filed a bug and the issue was fixed in jTDS’ CVS repository. Oh well.
Anyways, if you’re having issues browsing your MSSQL schemas with SquirrelSQL or other JDBC database tools, try building jTDS from source.