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?
Best Answer
sysfiles1is 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.
sysfiles1though 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: