SQL Server has System base tables which are hidden cannot be directly queried.

For example:

[sql]
SELECT * FROM sys.sysrscols
[/sql]

Output:

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘sys.sysrscols’.

In order to view data from the hidden System base tables you have to connect to SQL Server using Dedicated Administrator Connection.

In order to connect using Dedicated Administrator Connection, Add ADMIN: before the name of the serverinstance in SQL Server Management studio.

Dedicated Administrator Connection

Now you can query all the System tables. 🙂

[sql]

——————————————————————————————————————————————————
SELECT * FROM sys.sysrscols
GO
SELECT * FROM sys.sysrowsets
GO
SELECT * FROM sys.sysallocunits
GO
SELECT * FROM sys.sysfiles1
GO
SELECT * FROM sys.syspriorities
GO
SELECT * FROM sys.sysdbfrag
GO
SELECT * FROM sys.sysfgfrag
GO
SELECT * FROM sys.syspru
GO
SELECT * FROM sys.sysbrickfiles
GO
SELECT * FROM sys.sysphfg
GO
SELECT * FROM sys.sysprufiles
GO
SELECT * FROM sys.sysftinds
GO
SELECT * FROM sys.sysowners
GO
SELECT * FROM sys.sysdbreg
GO
SELECT * FROM sys.sysprivs
GO
SELECT * FROM sys.sysschobjs
GO
SELECT * FROM sys.syslogshippers
GO
SELECT * FROM sys.syscolpars
GO
SELECT * FROM sys.sysxlgns
GO
SELECT * FROM sys.sysxsrvs
GO
SELECT * FROM sys.sysnsobjs
GO
SELECT * FROM sys.sysusermsgs
GO
SELECT * FROM sys.syscerts
GO
SELECT * FROM sys.sysrmtlgns
GO
SELECT * FROM sys.syslnklgns
GO
SELECT * FROM sys.sysxprops
GO
SELECT * FROM sys.sysscalartypes
GO
SELECT * FROM sys.systypedsubobjs
GO
SELECT * FROM sys.sysidxstats
GO
SELECT * FROM sys.sysiscols
GO
SELECT * FROM sys.sysendpts
GO
SELECT * FROM sys.syswebmethods
GO
SELECT * FROM sys.sysbinobjs
GO
SELECT * FROM sys.sysaudacts
GO
SELECT * FROM sys.sysobjvalues
GO
SELECT * FROM sys.sysclsobjs
GO
SELECT * FROM sys.sysrowsetrefs
GO
SELECT * FROM sys.sysremsvcbinds
GO
SELECT * FROM sys.sysxmitqueue
GO
SELECT * FROM sys.sysrts
GO
SELECT * FROM sys.sysconvgroup
GO
SELECT * FROM sys.sysdesend
GO
SELECT * FROM sys.sysdercv
GO
SELECT * FROM sys.syssingleobjrefs
GO
SELECT * FROM sys.sysmultiobjrefs
GO
SELECT * FROM sys.sysguidrefs
GO
SELECT * FROM sys.syschildinsts
GO
SELECT * FROM sys.syscompfragments
GO
SELECT * FROM sys.sysftstops
GO
SELECT * FROM sys.sysqnames
GO
SELECT * FROM sys.sysxmlcomponent
GO
SELECT * FROM sys.sysxmlfacet
GO
SELECT * FROM sys.sysxmlplacement
GO
SELECT * FROM sys.sysobjkeycrypts
GO
SELECT * FROM sys.sysasymkeys
GO
SELECT * FROM sys.syssqlguides
GO
SELECT * FROM sys.sysbinsubobjs
GO
SELECT * FROM sys.syssoftobjrefs
GO
[/sql]

2 Replies to “How To View Data in Hidden System Tables.”

  1. Hi gakkula,

    I tried the above couldn’t succedded, resulted in error stating that server not found or unavailable.

    please suggest.

    Thanks,
    Prabhu

    1. Hi Prabhu,

      Use ADMIN:servernameinstancename

      For Example:
      if the server name is servertest and the instance name is SQL1
      then use

      ADMIN:servertestSQL1

      Please note:
      1. Dedicated Administrator Connection is only allowed from a client running on the server. By default network connections are not supported.

      2. Only users with SQL Server sysadmin role can use DAC.

      Please refer to below link for additional details.
      http://msdn.microsoft.com/en-us/library/ms189595(v=sql.105).aspx

      Thanks,
      Devi Prasad.

Leave a Reply

Your email address will not be published. Required fields are marked *