SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by VM / sp_tables / Latest Posts

RE: sp_tables

UMG Developer — Fri, 03 Dec 2010 13:41:41 GMT

Nice question, thanks!

RE: sp_tables

croberts 36762 — Thu, 02 Dec 2010 12:26:09 GMT

Thanks Hugo. I have forgotten how to read.

RE: sp_tables

Hugo Kornelis — Thu, 02 Dec 2010 12:20:54 GMT

You're doing nothing wrong. This is the expected and documented behaviour for sys.tables.Note that this discussion is about the system stored procedure [i]sp_tables[/i], not about the catalog view [i]sys.tables[/i].

RE: sp_tables

croberts 36762 — Thu, 02 Dec 2010 08:33:02 GMT

When I do a 'select * from sys.tables' on a database with views, only the tables are returned, not the views. This is on SQL 2005. What am I doing wrong? Can someone verify this?

RE: sp_tables

L' Eomot Inversé — Wed, 01 Dec 2010 05:42:06 GMT

Nice straightforward question, no tricks or traps. Pity that the MS documentation has fallen out of date, though.

RE: sp_tables

Koen Verbeeck — Wed, 01 Dec 2010 00:37:22 GMT

[quote][b]payalvyas11 (11/30/2010)[/b][hr]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.[/quote]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.

RE: sp_tables

jaganmohan.rao — Tue, 30 Nov 2010 23:44:03 GMT

Good question. Keep it up.

RE: sp_tables

payal vyas — Tue, 30 Nov 2010 11:00:35 GMT

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.

RE: sp_tables

SanDroid — Tue, 30 Nov 2010 10:12:06 GMT

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:

RE: sp_tables

Koen Verbeeck — Tue, 30 Nov 2010 10:04:42 GMT

Nice question, thanks.

RE: sp_tables

Hugo Kornelis — Tue, 30 Nov 2010 08:36:19 GMT

[quote][b]sknox (11/30/2010)[/b][hr]Hugo, I do hope that you've posted the same excellent analysis as a comment to the Microsoft documentation... :-P[/quote]Thanks! ;-)And no, I did not. But I did submit it as feedback to the article, so that it will hopefully be fixed.

RE: sp_tables

adrian.facio — Tue, 30 Nov 2010 07:45:38 GMT

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".

RE: sp_tables

sknox — Tue, 30 Nov 2010 07:40:39 GMT

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

RE: sp_tables

Hugo Kornelis — Tue, 30 Nov 2010 01:52:01 GMT

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 [url=http://msdn.microsoft.com/en-us/library/ms186250.aspx]here[/url]), 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 [b]some[/b] 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;-)).

RE: sp_tables

Yousaf Khan — Tue, 30 Nov 2010 01:10:38 GMT

Check the bellow link for this topichttp://searchsqlserver.techtarget.com/tip/Stored-procedure-List-SQL-Server-database-objects-by-selected-typesYousaf Khan

RE: sp_tables

sharath.chalamgari — Tue, 30 Nov 2010 00:11:51 GMT

simple one today ;)

RE: sp_tables

Nakul Vachhrajani — Mon, 29 Nov 2010 23:07:49 GMT

Straight-forward, simple, yet important question.Thanks!

RE: sp_tables

SQLRNNR — Mon, 29 Nov 2010 23:03:44 GMT

Thanks for the question.

RE: sp_tables

Hardy21 — Mon, 29 Nov 2010 21:28:52 GMT

Very straight forward question. Thanks.

sp_tables

VM-723206 — Mon, 29 Nov 2010 21:19:43 GMT

Comments posted to this topic are about the item [B]sp_tables[/B]