Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

sp_tables Expand / Collapse
Author
Message
Posted Monday, November 29, 2010 9:19 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 6:34 AM
Points: 654, Visits: 265
Comments posted to this topic are about the item sp_tables
Post #1027714
Posted Monday, November 29, 2010 9:28 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:42 AM
Points: 890, Visits: 1,179
Very straight forward question. Thanks.

Thanks
Post #1027716
Posted Monday, November 29, 2010 11:03 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 20,458, Visits: 14,082
Thanks for the question.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1027747
Posted Monday, November 29, 2010 11:07 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 10:17 PM
Points: 1,381, Visits: 1,774
Straight-forward, simple, yet important question.
Thanks!


Thanks & Regards,
Nakul Vachhrajani.
http://beyondrelational.com/modules/2/blogs/77/nakuls-blog.aspx
Be courteous. Drive responsibly.

Follow me on
Twitter: @nakulv_sql
Google Plus: +Nakul
Post #1027750
Posted Tuesday, November 30, 2010 12:11 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:33 AM
Points: 1,128, Visits: 756
simple one today ;)
Post #1027773
Posted Tuesday, November 30, 2010 1:10 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Saturday, December 28, 2013 4:34 AM
Points: 407, Visits: 75
Check the bellow link for this topic

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

Yousaf Khan
Post #1027792
Posted Tuesday, November 30, 2010 1:52 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 5,794, Visits: 8,006
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1027798
Posted Tuesday, November 30, 2010 7:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:06 AM
Points: 1,245, Visits: 1,592
Hugo, I do hope that you've posted the same excellent analysis as a comment to the Microsoft documentation...
Post #1027989
Posted Tuesday, November 30, 2010 7:45 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, April 11, 2014 1:43 PM
Points: 335, Visits: 367
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".
Post #1027993
Posted Tuesday, November 30, 2010 8:36 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:32 PM
Points: 5,794, Visits: 8,006
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1028018
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse