{"id":262,"date":"2013-01-02T11:38:28","date_gmt":"2013-01-02T11:38:28","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2013\/01\/02\/how-to-view-data-in-hidden-system-tables\/"},"modified":"2013-01-02T11:38:28","modified_gmt":"2013-01-02T11:38:28","slug":"how-to-view-data-in-hidden-system-tables","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2013\/01\/02\/how-to-view-data-in-hidden-system-tables\/","title":{"rendered":"How To View Data in Hidden System Tables."},"content":{"rendered":"

SQL Server has System base tables which are hidden cannot be directly queried.<\/p>\n

For example:<\/p>\n

[sql]
\nSELECT * FROM sys.sysrscols
\n[\/sql]<\/p>\n

Output:
\n
\nMsg 208, Level 16, State 1, Line 1
\nInvalid object name ‘sys.sysrscols’.
\n<\/font><\/p>\n

In order to view data from the hidden System base tables you have to connect to SQL Server using Dedicated Administrator Connection.<\/p>\n

In order to connect using Dedicated Administrator Connection, Add ADMIN:<\/strong> before the name of the serverinstance in SQL Server Management studio.<\/p>\n

\"Dedicated<\/a><\/p>\n

Now you can query all the System tables. \ud83d\ude42<\/p>\n

[sql]<\/p>\n

——————————————————————————————————————————————————
\nSELECT * FROM sys.sysrscols
\nGO
\nSELECT * FROM sys.sysrowsets
\nGO
\nSELECT * FROM sys.sysallocunits
\nGO
\nSELECT * FROM sys.sysfiles1
\nGO
\nSELECT * FROM sys.syspriorities
\nGO
\nSELECT * FROM sys.sysdbfrag
\nGO
\nSELECT * FROM sys.sysfgfrag
\nGO
\nSELECT * FROM sys.syspru
\nGO
\nSELECT * FROM sys.sysbrickfiles
\nGO
\nSELECT * FROM sys.sysphfg
\nGO
\nSELECT * FROM sys.sysprufiles
\nGO
\nSELECT * FROM sys.sysftinds
\nGO
\nSELECT * FROM sys.sysowners
\nGO
\nSELECT * FROM sys.sysdbreg
\nGO
\nSELECT * FROM sys.sysprivs
\nGO
\nSELECT * FROM sys.sysschobjs
\nGO
\nSELECT * FROM sys.syslogshippers
\nGO
\nSELECT * FROM sys.syscolpars
\nGO
\nSELECT * FROM sys.sysxlgns
\nGO
\nSELECT * FROM sys.sysxsrvs
\nGO
\nSELECT * FROM sys.sysnsobjs
\nGO
\nSELECT * FROM sys.sysusermsgs
\nGO
\nSELECT * FROM sys.syscerts
\nGO
\nSELECT * FROM sys.sysrmtlgns
\nGO
\nSELECT * FROM sys.syslnklgns
\nGO
\nSELECT * FROM sys.sysxprops
\nGO
\nSELECT * FROM sys.sysscalartypes
\nGO
\nSELECT * FROM sys.systypedsubobjs
\nGO
\nSELECT * FROM sys.sysidxstats
\nGO
\nSELECT * FROM sys.sysiscols
\nGO
\nSELECT * FROM sys.sysendpts
\nGO
\nSELECT * FROM sys.syswebmethods
\nGO
\nSELECT * FROM sys.sysbinobjs
\nGO
\nSELECT * FROM sys.sysaudacts
\nGO
\nSELECT * FROM sys.sysobjvalues
\nGO
\nSELECT * FROM sys.sysclsobjs
\nGO
\nSELECT * FROM sys.sysrowsetrefs
\nGO
\nSELECT * FROM sys.sysremsvcbinds
\nGO
\nSELECT * FROM sys.sysxmitqueue
\nGO
\nSELECT * FROM sys.sysrts
\nGO
\nSELECT * FROM sys.sysconvgroup
\nGO
\nSELECT * FROM sys.sysdesend
\nGO
\nSELECT * FROM sys.sysdercv
\nGO
\nSELECT * FROM sys.syssingleobjrefs
\nGO
\nSELECT * FROM sys.sysmultiobjrefs
\nGO
\nSELECT * FROM sys.sysguidrefs
\nGO
\nSELECT * FROM sys.syschildinsts
\nGO
\nSELECT * FROM sys.syscompfragments
\nGO
\nSELECT * FROM sys.sysftstops
\nGO
\nSELECT * FROM sys.sysqnames
\nGO
\nSELECT * FROM sys.sysxmlcomponent
\nGO
\nSELECT * FROM sys.sysxmlfacet
\nGO
\nSELECT * FROM sys.sysxmlplacement
\nGO
\nSELECT * FROM sys.sysobjkeycrypts
\nGO
\nSELECT * FROM sys.sysasymkeys
\nGO
\nSELECT * FROM sys.syssqlguides
\nGO
\nSELECT * FROM sys.sysbinsubobjs
\nGO
\nSELECT * FROM sys.syssoftobjrefs
\nGO
\n[\/sql]<\/p>\n","protected":false},"excerpt":{"rendered":"

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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[101,1908,3],"tags":[],"class_list":["post-262","post","type-post","status-publish","format-standard","hentry","category-sql-security","category-sql-server-tricks","category-sqlserverpedia-syndication"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/262","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=262"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/262\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=262"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=262"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=262"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}