Which database stores sys.tables or sys.indexes in SQL Server?

Question

I am not able to figure out where these system tables are stored, your help will be much appreciated.

Answer 1

These are views.

The view definitions are held in the hidden resource database but they access system base tables held in each individual database.

You can use sp_helptext to see the definition of sys.indexes (and that it accesses sys.sysidxstats, sys.syssingleobjrefs, sys.syspalvalues) but attempting this with sys.objects just shows a SELECT against sys.objects$.

If you view the execution plan for a select against sys.objects you can see the objects accessed however.

  |--Hash Match(Right Outer Join, HASH:([n].[value])=([o].[type]), RESIDUAL:([mssqlsystemresource].[sys].[syspalnames].[value] as [n].[value]=[foo].[sys].[sysschobjs].[type] as [o].[type]))
       |--Clustered Index Seek(OBJECT:([mssqlsystemresource].[sys].[syspalnames].[cl] AS [n]), SEEK:([n].[class]='OBTY') ORDERED FORWARD)
       |--Hash Match(Right Outer Join, HASH:([r].[depid])=([o].[id]))
            |--Index Scan(OBJECT:([foo].[sys].[syssingleobjrefs].[nc1] AS [r]),  WHERE:([foo].[sys].[syssingleobjrefs].[class] as [r].[class]=(97) AND [foo].[sys].[syssingleobjrefs].[depsubid] as [r].[depsubid]=(0)))
            |--Filter(WHERE:(has_access('CO',[foo].[sys].[sysschobjs].[id] as [o].[id])=(1)))
                 |--Compute Scalar(DEFINE:([Expr1006]=CONVERT(bit,[foo].[sys].[sysschobjs].[status] as [o].[status]&(1),0), [Expr1009]=CONVERT(bit,[foo].[sys].[sysschobjs].[status] as [o].[status]&(16),0), [Expr1010]=CONVERT(bit,[foo].[sys].[sysschobjs].[status] as [o].[status]&(64),0)))
                      |--Clustered Index Scan(OBJECT:([foo].[sys].[sysschobjs].[clst] AS [o]), WHERE:([foo].[sys].[sysschobjs].[nsclass] as [o].[nsclass]=(0) AND [foo].[sys].[sysschobjs].[pclass] as [o].[pclass]=(1)))

But the system base tables cannot be queried directly except via the DAC so this information is only rarely useful to know.

Answer 2

All sys objects are stored in resource database and are logically visible under sys schema of each database.

Example :

Select * from Database.sys.sysaltfiles will give you results despite this view not in that database.