3

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?

1
  • 1
    If you connect via the DAC then select * from sys.sysfiles1 will work. Commented Jan 4, 2012 at 15:06

3 Answers 3

5

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. See https://learn.microsoft.com/en-us/archive/blogs/sqlserverstorageengine/fragmentation-part-4-what-are-heaps:

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

0
5

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.

4
  • In 2008 these are exposed indirectly through views, though, right? I think that's what he's asking.
    – JNK
    Commented Jan 4, 2012 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
    Commented Jan 4, 2012 at 15:08
  • It shows up in sys.objects but you can (normally) only access them through the DMVs and catalog views
    – JNK
    Commented Jan 4, 2012 at 15:13
  • @JNK: or on the DAC
    – gbn
    Commented Jan 4, 2012 at 15:16
1

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
  • 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
    Commented Jan 4, 2012 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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