Why does 'sysfiles1' show up in the sys.objects catalog view in SQL Server 2008?

Question

We were recently using this query to find tables in our database that didn't have clustered indexes and found that one of the results it reported back was the 'sys.sysfiles1' table. We're running SQL Server 2008 and I was under the impression that this table wasn't used anymore (as some answers have pointed out there seems to be a misconception that this table only exists for databases upgraded from SQL Server 2000). Furthermore, I'm not able to directly select anything out of the sys.sysfiles1 table (though I am able to select directly out of the sys.sysfiles view).

Running the following to create a fresh database against a local installation of SQL Server 2008 (@@version = Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64), though we've seen it on our production instance of SQL Server 2008 as well) illustrates what I mean:

CREATE DATABASE SysFilesTesting
--returns a row:
SELECT * FROM SysFilesTesting.sys.objects where name = 'sysfiles1'
--throws 'invalid object name' error:
SELECT * FROM SysFilesTesting.sys.sysfiles1

Why is sys.objects reporting the existence of a sysfiles1 table?

Answer 1

sysfiles1 is an internal catalog table. There are many of them (basically any table with object_id between 1 and 99 is, by definition, an internal catalog table). The query compiler refuses to bind queries to them under normal context, so if you try to select from them you get the error you see. DAC connections can bind to them and retrieve data, but you still won't be able to update them.

This is all described on MSDN, see System Base Tables.

sysfiles1 though is a special case, not documented in the link above. This catalog table exists only in databases upgraded from SQL 2000. See http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/09/19/761437.aspx:

... sysfiles1 table is a linked-heap. This table contains the locations of the files comprising the database ...

Answer 2

There are lots of them

SELECT * FROM sys.objects where name LIKE 'sys%'

These really are objects in your databases
Internally, other useful objects like sys.objects don't actually exist in your database.

Answer 3

Check the type of the object sysfiles1 like this:

SELECT name, type FROM SysFilesTesting.sys.objects where name = 'sysfiles1'

---

These are the complete list of types:

AF = Aggregate function (CLR)

C = CHECK constraint

D = DEFAULT (constraint or stand-alone)

F = FOREIGN KEY constraint

FN = SQL scalar function

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued function

IF = SQL inline table-valued function

IT = Internal table

P = SQL Stored Procedure

PC = Assembly (CLR) stored-procedure

PG = Plan guide

PK = PRIMARY KEY constraint

R = Rule (old-style, stand-alone)

RF = Replication-filter-procedure

S = System base table

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = SQL table-valued-function

TR = SQL DML trigger

TT = Table type

U = Table (user-defined)

UQ = UNIQUE constraint

V = View

X = Extended stored procedure

---

As @JNK stated, it's just an internal table that you can only query it directly through the DAC or indirectly through system views.

---