Start a new topic

SQL Server system tables and system views

[This topic is migrated from our old forums. The original author name has been removed]

Using SQL Server 2008 R2 via jTDS, I see we have a "system tables" entry in the object tree for every database but it is always empty. Even in the msdb database, all the sys-prefixed tables are in the normal table list rather than system tables. I presume this used to hold things like sysobjects, syscolumns, etc before Microsoft changed them to views and created the newer sys.objects and sys.columns versions. Oddly though - all views seem to be treated equal in DBVisualiser, with user-created views sitting in the list alongside INFORMATION_SCHEMA views and SYS views. I'm not sure whether the best solution for most users would be to remove the system tables entry and have all tables show in the same list like views or to add a system views entry that displays INFORMATION_SCHEMA views and SYS views seperately. My personal preference would be the latter as it would let me filter the cruft from the view list without having to resort to yet more filters. Either way though, the current approach seems to be inconsistent between how tables and views are handled.

[This reply is migrated from our old forums.]

Re: SQL Server system tables and system views
Hi Vince, Thanks for your feedback. We currently ask the JDBC driver for "tables" , "system tables" and "views" and this is what it returns. The SQL Server drivers do not provide "system views" as a valid type, hence the tree layout. I agree that it looks odd if System Tables always is empty. Do you happen to know when the change to views that you mention happened? I wil open a ticket to look into if there's a better way to deal with this. Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: SQL Server system tables and system views
It was in SQL Server 2005. The old system tables are still emulated for compatibility but are now actually views with the "sys" schema. The new views they are replaced by all have the "INFORMATION_SCHEMA" schema. http://msdn.microsoft.com/en-us/library/ms187997%28v=sql.90%29.aspx http://msdn.microsoft.com/en-us/library/ms177862%28v=sql.90%29.aspx http://msdn.microsoft.com/en-us/library/ms186778%28v=sql.90%29.aspx
[This reply is migrated from our old forums.]

Re: SQL Server system tables and system views
Vince, We just launched the BETA testing of the upcoming 9.5 version with a few enhancements for SQL Server. It would be great if you can give it a test and verify. http://www.dbvis.com/download/beta/ For more information about the news in the BETA version: http://www.dbvis.com/doc/relnotes/index.jsp?showheader=false&showtoc=false&version=9.5-BETA We plan to go public with 9.5 in a few weeks. Regards Roger
Login or Signup to post a comment