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: http://sqlblog.com/blogs/hugo_kornelis