System Base Tables

Comments: No Comments
Published on: January 30, 2012

On January 19th, I published a post about the Dedicated Administrator Connection.  I spoke very briefly of the system base tables in that article.  Today, I want to dive into these tables a little bit more.

First, let’s get the Microsoft definition for these tables.  “System base tables are the underlying tables that actually store the metadata for a specific database.”

Have you ever queried sys.objects from the master database and wondered about some of the results?  You can see all of the System base tables when querying the sys.objects view.  These tables are denoted in sys.objects by type of ‘S’ and a type_desc of ‘SYSTEM_TABLE’.

Here is a simple query to take a quick peek at these tables.

[codesyntax lang="tsql"]

[/codesyntax]

There is no need for a Dedicated Administrator connection in order for this query to work.  You can view these results with a non-DAC connection to the instance so long as you have adequate permissions to query sys.objects.  That said, not all objects returned by that query are System Base Tables.  Furthermore, it appears that the list from MSDN is not comprehensive.  One such example is the reference to sys.sysserrefs that does not appear to exist in SQL 2008 R2 and the missing System Base table called sys.sysbrickfiles (which is used by sysaltfiles as shown in this execution plan).

If I try to query the sysbrickfiles table (as an example) without connecting via DAC, I will get an error message like this:

This is normal behavior.  You cannot query the system base tables without first connecting via DAC.  Having said that, the obligatory warning is required.  As explained on MSDN, these tables are intended for use by Microsoft.  Proceed at your own risk and please make sure you have backups.

In addition to these System Base tables, you will find tables not mentioned in the article nor in the master database.  These System Base tables are found within the Resource database.  The resource database does contain most of the tables mentioned in that article, but there are some differences.  I will leave that discovery exercise to the reader.

There is plenty about SQL Server that many of us take for granted.  Under the hood, there is much more to learn.  Taking a peek at the System Base tables is one of those areas that will help you to learn more about SQL Server.  My question is this: How far are you willing to explore to learn more about SQL Server?

No Comments - Leave a comment

Leave a comment

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">






Calendar
January 2012
M T W T F S S
« Dec   Feb »
 1
2345678
9101112131415
16171819202122
23242526272829
3031  
Content
SQLHelp

SQLHelp

  • @SQLTaiob: @pshore73 It all depends if you can sustain index not being there. If I have any doubt I do online=on and use higher maxdop. #sqlhelp
  • @pshore73: @SQLTaiob #SQLHelp the plan is to do them one at a time during off hours, it is a DB for vCenter so it is never really offline
  • @SQLTaiob: @pshore73 If you are ok with dropping the index and users not being impacted yes drop/create, otherwise I will do online=on. #sqlhelp
  • @pshore73: #SQLHelp 500+MB indexes, 90+% fragmentation, in terms of time, t-log use & proc use am I better off doing a rebuild or drop/create
  • @regbac: @NikoNeugebauer yes there is... UPDATE Subscriptions SET ExtensionSettings = ... FROM ReportServer.dbo.Subscriptions. #sqlhelp #ssrs
  • @NikoNeugebauer: Is there a way to modify SharePoint Reporting Services subscription via t-sql ? (Native & SharePoint) #sqlhelp #ssrs #ssrshelp
  • @SQLSoldier: @SQLDBA @SQLYard If not connected to the internet, will need to download manually and specify an alternate path for it. #sqlhelp
  • @SQLDBA: @SQLYard That's .NET framework 3.5. Enable through Windows features. #sqlhelp
  • @SQLYard: #sqlhelp, Issue installing SQL on a VM 2012 Please try enabling Windows feature :NetFx3. I have tried most of the fixes on the web.Thanks
  • @sqlconcepts: Dear #sqlhelp, does anyone have a bit of code that will help me estimate the space requirement for Change Tracking on a table?

Welcome , today is Tuesday, September 30, 2014