Post reply

sp_tables

  • November 29, 2010 at 9:19 pm

    #232053

    Comments posted to this topic are about the item sp_tables

  • November 29, 2010 at 9:28 pm

    #1256577

    Very straight forward question. Thanks.

    Thanks

  • November 29, 2010 at 11:03 pm

    #1256600

    Thanks for the question.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 29, 2010 at 11:07 pm

    #1256602

    Straight-forward, simple, yet important question.

    Thanks!

    Thanks & Regards,
    Nakul Vachhrajani.
    http://nakulvachhrajani.com

    Follow me on
    Twitter: @sqltwins

  • November 30, 2010 at 12:11 am

    #1256620

    simple one today 😉

  • November 30, 2010 at 1:10 am

    #1256636

    Check the bellow link for this topic

    http://searchsqlserver.techtarget.com/tip/Stored-procedure-List-SQL-Server-database-objects-by-selected-types

    Yousaf Khan

  • November 30, 2010 at 1:52 am

    #1256642

    An interesting question.

    I will not debate the quality of the question, but I will debate the quality of the documentation. Not the documentation that is linked to (this is a very old description, pointing to the SQL Server 2000 version of Books Online), but to the current version (found here), that is almost identical.

    The description in BOL explicitly mentions the three types that were also the answer to this question: tbales, views, and system tables. But as of SQL Server 2005, system tables no longer exist - or rather, they do exist, but are completely hidden from view. They have been replaced by system views. These are returned by sp_tables - as views, not as system tables. So since SQL Server 2005, sp_tables no longer returns objects of the type SYSTEMTABLE.

    The same description in BOL also says that sp_tables, and I quote, "Returns a list of objects that can be queried in the current environment. This means any object that can appear in a FROM clause, except synonym objects". If that were true, then some user-defined functions should be included as well, namely all table-valued functions. This is not the case.

    My guess is that sp_tables has not been touched since SQL Server 2000. Neither the procedure, nor the documentation has been changed. And noone has noticed that, in spite of the lack of changes, external changes have made the documentation go out of sync.

    Thanks for the interesting question, VM - I had never heard of this system stored procedure before. (And I'll probably forget about it within a few days;-)).

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • November 30, 2010 at 7:40 am

    #1256799

    Hugo, I do hope that you've posted the same excellent analysis as a comment to the Microsoft documentation... 😛

  • November 30, 2010 at 7:45 am

    #1256802

    Oh men, i was thinking about table valued functions and i answer incorrectly, they can be in a "from" clause. I was being very literal, surely msdn did not mean an absolute "everything that can be in a from clause".

  • November 30, 2010 at 8:36 am

    #1256819

    sknox (11/30/2010)

    Hugo, I do hope that you've posted the same excellent analysis as a comment to the Microsoft documentation... 😛

    Thanks! 😉

    And no, I did not. But I did submit it as feedback to the article, so that it will hopefully be fixed.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • November 30, 2010 at 10:04 am

    #1256882

    Nice question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • November 30, 2010 at 10:12 am

    #1256886

    Nice Question!

    I liked the way I learned something from the answer.

    Never tought before about how it lists System Tables as a TABLE object even though they are differant from user created TABLE objects.:cool:

  • November 30, 2010 at 11:00 am

    #1256920

    I too answered incorrectly after reading the documentation of msdn. The statment "any object that can appear in a FROM clause, except synonym objects", made me assume that user defined functions too would be included.

  • November 30, 2010 at 11:44 pm

    #1257168

    Good question. Keep it up.

    Regards,
    Jagan.

  • December 1, 2010 at 12:37 am

    #1257177

    payalvyas11 (11/30/2010)

    I too answered incorrectly after reading the documentation of msdn. The statment "any object that can appear in a FROM clause, except synonym objects", made me assume that user defined functions too would be included.

    True, the documentation can be confusing, but if you've read further, you would have noticed by result sets: TABLE_TYPE --> table, system table or view.

    This leaves very little doubt of course.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply