0

I often run into MSSQL databases that have many more tables than are listed in information_schema or systables.

For example, I'm querying a database right now but only getting the tables spt_fallback_db, spt_fallback_dev, spt_fallback_usg, spt_monitor, spt_values. (1)

How does this happen?

And - can it be fixed easily?

(1) I should clarify that this isn't a permissions issue, as I am sysadmin on the database ; there are around 200 tables and I have full permission on all of them.

Community
  • 1
  • 1
rlb.usa
  • 14,942
  • 16
  • 80
  • 128
  • 1
    Your results look like they might be names of databases, not tables. Can you include the SQL query that you are running to get those results? – DeadZone Mar 19 '15 at 18:52
  • 1
    Another thought... Does your connection accidentally have `SET ROWCOUNT 5;` turned on? (Does any query return more than 5 rows?) – DeadZone Mar 19 '15 at 18:54

1 Answers1

0

@DeadZone was right on the money, the query had some issues. I was using:

DECLARE @command varchar(1000) 
SELECT @command = 'SELECT * FROM sysobjects WHERE xtype=''U'' ' 
EXEC sp_MSforeachdb @command

But it would only show system tables. So then I switched to a more direct query to see what was going on and was able to view the tables:

use MYDATABASENAME;
SELECT * FROM sysobjects WHERE xtype='U'
rlb.usa
  • 14,942
  • 16
  • 80
  • 128