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:

--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?

  • 1
    If you connect via the DAC then select * from sys.sysfiles1 will work. – Martin Smith Jan 4 '12 at 15:06
  • Furthermore, I'm not able to select anything out of it (though I am able to select out of sys.sysfiles1). What table are you talking about in the first half of this sentence? – Nick Chammas Jan 4 '12 at 19:10
  • That sentence had a typo and was poorly phrased. I've revised it. – Jesse Taber Jan 5 '12 at 3:51

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

  • Just a nit, I don't agree about the 2000 bit... sysfiles1 shows up in sys.objects on several of my systems, and I can assure you that none of them were upgraded from 2000 (all I've tested so far are either 2012 RC0 or 2008 R2, and all are clean installs). Nothing in the link suggests anything about the presence or absence of that item based on upgrading... – Aaron Bertrand Jan 4 '12 at 20:38
  • @Aaron: You are correct. Actually, after I dig into it, it appears sysfiles1 is still used (populated) today (SQL 2012). – Remus Rusanu Jan 4 '12 at 21:53

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.

  • In 2008 these are exposed indirectly through views, though, right? I think that's what he's asking. – JNK Jan 4 '12 at 15:03
  • 1
    As I understand it, anything that is a known catalog view or compatibilty view doesn't show up here. These are mostly real internal or legacy stuff. eg syscolpars can be hacked hackingsqlserver.com – gbn Jan 4 '12 at 15:08
  • It shows up in sys.objects but you can (normally) only access them through the DMVs and catalog views – JNK Jan 4 '12 at 15:13
  • @JNK: or on the DAC – gbn Jan 4 '12 at 15:16

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.

  • 1
    It is, it's just an internal table so you can only query it directly through the DAC or indirectly through system views. – JNK Jan 4 '12 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.