SQL Server Performance

In SQL Server 2005, the system tables are gone.

Discussion in 'SQL Server 2005 General DBA Questions' started by mmarovic, May 19, 2005.

  1. mmarovic Active Member

    System data in SQL Server 2005 is stored in hidden "resource" tables, which can only be accessed directly by the server. http://www.searchSQLServer.com/tip/1,289483,sid87_gci1089808,00.html?track=NL-464&ad=514313HOUSE
  2. FrankKalis Moderator

    So what? [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  3. derrickleggett New Member

    They've been promising to do this for YEARS now. People should have listened to them. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />BTW, you can still access the "tables". They are views now instead though; however, all the code you have written to do lookups should theoretically still work (such as querying sysobjects and syscolumns).<br /><br />MeanOldDBA<br />derrickleggett@hotmail.com<br /><br />When life gives you a lemon, fire the DBA.
  4. mmarovic Active Member

    Just sign on the wall. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  5. rockmoose New Member

    When is the discussion:
    catalog views vs. INFORMATION_SCHEMA views in SQL2005 coming up...
  6. FrankKalis Moderator

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />
  7. SQL_Guess New Member

    It will be interesting to see how many home grown DBA applications have problems, for example Log Shipping, custom procedure's looking for long lockers and so on and so forth....

    I hope they extend the INFORMATION_SCHEMA to cover things it doesn't at the moment (like defaults and indexes)

    Panic, Chaos, Disorder ... my work here is done --unknown
  8. satya Moderator

    The log shipping may fade out in SQL 2005 as the database mirroring helps a lot as compared to log shipping. So in this case using custom stored procedures must be tested at all the time as compared to SQL 2000 and 2005.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  9. Lee Dise New Member

    > They've been promising to do this for YEARS now. People should have listened to them

    I guess I don't get it. The INFORMATION_SCHEMA stuff I've looked at in SQL Server 2000 wasn't in depth enough to pull a terrific amount of useful information from; much if not most of the time, there wasn't a good alternative to using the system tables. So now, they're taking them away. Worse than that, the old sys tables really still *there* but out of spite they're denying even DBAs the right to use them. That's wounding you *and* rubbing salt in it.

    I guess that's good business practice, in a mean, spiteful, "the customer is always wrong" sort of way.

    And you know -- I'll bet the new schema *still* won't contain everything I need.

    Is it too late to convert to Oracle?

    > BTW, you can still access the "tables". They are views now instead though

    Hmmm. I read an article that said not even DBAs could access them -- that only "the server" (however that was meant) could do so. I guess I don't know how to interpret all that.


  10. FrankKalis Moderator

    Let's wait and see what there is to come. Finally you can judge at that moment when you have your RTM copy installed. So, right now much ado about nothing. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  11. Lee Dise New Member

    You're right, of course, Frank. I'm thinking it could be a trial balloon -- announce that the system tables won't be available and then base the final decision on how intense the screaming is. <img src='/community/emoticons/emotion-1.gif' alt=':)' />
  12. derrickleggett New Member

    Lee, the views that reference the old tables are available. They are all sys.systable. You can grant someone access to any of them. In addition, the new views and functions are actually far more powerful than the old tables. This is a good improvement, even if it causes some pain initially. Sometimes change is aggrivating.

    MeanOldDBA
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.

Share This Page