Search code examples
sql-serverinformation-schema

How does a table not being in information_schema or systables happen?

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.

Solution

  • @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'