Geek Answers Handbook

Why are some system tables defined as a "fake table" using the ObjectIpperty "TableIsFake"?

I looked at the ObjectProperty list today and stumbled upon the TableIsFake property. The name amused me, so I looked that it checks:

The table is not real. It materializes at the request of SQL Server.

What exactly does this mean? For example, when I run the following query:

 SELECT [name], xtype FROM dbo.sysobjects WHERE OBJECTPROPERTY(object_id([name]), N'TableIsFake') = 1 ORDER BY [name]

I get the following results:

 name xtype -------------- ----- sysfiles S sysforeignkeys S sysindexkeys S sysmembers S sysprotects S

But if I query the database for system tables:

 SELECT [name], xtype FROM dbo.sysobjects WHERE xtype = 'S' ORDER BY [name]

I get the following system tables:

 name xtype ------------------- ----- syscolumns S syscomments S sysdepends S sysfilegroups S sysfiles S sysfiles1 S sysforeignkeys S sysfulltextcatalogs S sysfulltextnotify S sysindexes S sysindexkeys S sysmembers S sysobjects S syspermissions S sysproperties S sysprotects S sysreferences S systypes S sysusers S

What makes the sysfiles , sysforeignkeys , sysindexkeys , sysmembers , sysprotects system tables "fake"? Or else, what does it mean that they are "materialized internally at the request of SQL Server"? Does this mean that they are created only when the process needs it, or do I call something like SELECT * FROM sysfiles ?

+4
source share
2 answers

A fake table is a special in-memory structure without being saved to disk. SQL Server creates it on demand.

The best example is sysprocesses.

IMHO: the key "persistence of the disk":

  • sysusers or sysxlogins (for example) are real tables in each db / master respectively and backup / restore survives
  • sysprocesses or sysfiles will not be able to withstand backup / restore because there is nothing to write or read on disk.
+4
source

The sysfiles table is really a view and is created in such a way that for backward compatibility see Displaying system tables in system views to find out what you should use

So instead of sysfiles you should use sys.database_files

+1
source

More articles:


All Articles