By now, I am sure that you are well aware of the Resource Database.  Starting with SQL Server 2005, a new system database was made available – the Resource Database.  In SQL 2005 you could move the Resource Database – but in 2008 and beyond you cannot.  If you plan your install accordingly, your databases should install where you would like them (i.e. the program files directory that is the default installation directory can be changed during setup).

That is all well and good, but what is in this database and how can we take a look into it?  I thought about this as I was reading a blog by Thomas LaRock where he discusses system tables and system views.  I started thinking about the underpinnings of these views and if we could see them in the resource databse.  Taking a peek into the Resource Database is rather trivial.  I will show you how to copy that database and attach it as an alternate database.

The first step in taking a peek is to locate the directory where the data and log file for the hidden database are stored.  You can find the path by referring back to the SQL 2005 and 2008 links earlier in this post.  In my case, the path (I am using SQL 2008 SP2) for me is displayed in the next image.

Take the two highlighted files, copy them and place them into a new directory.  I copied and renamed the files – placing the files into my “Data” directory as follows.

As you can see, the database is rather small – and you could probably do this procedure multiple times .

Once you have the files copied to the new locations, you are now ready to attach the database.  I am just using the GUI to attach the database – for illustrative purposes.  While attaching, I think there is an interesting thing that should stand out.

Now look closely at the image just above – pay attention to the current file path.  This is the screen you will see after you browse to your new directory location and choose to add the mdf file for the Resource database.  You will see that this file path does not match either of the file paths that I have already shown.  Take a closer look:

You can see that the file path references a path for e:\sql10_katmai_t\sql…  I find that pretty interesting.  I don’t know the reason for that path persisting like that.  The file paths shown need to be changed to the appropriate file path where we placed the copied files – as follows.

There is one more thing that I think you should change.  This really is a matter of preference and is good housekeeping.  Change the name of the database in the Attach As field.  I appended _test to the end of the database name.  This should make it obvious that this database is NOT the system database.

With these few steps, you now have the resource database attached as a User Database.  With that, you now can take a look at the inner makings of some of the system views.  Take the following queries for example:

Code block

SELECT * FROM master.dbo.sysdatabases
SELECT * FROM mssqlsystemresource_test.sys.sysdatabases

Both of these queries return exactly the same thing.  Both of these queries are only provided as a means of backwards compatibility.  You can actually see master.sys.sysdatabases (just like mssqlsystemresource_test.sys.sysdatabases).  One thing you can do though is script out that view and see what the source of the data is.  Well…with the resource database attached as described, you can now script it out.  When you script out the view, make sure you try to script the correct version of it.  Just like any user database, you will get a category of system tables and one of system views.  The objects under those categories cannot be scripted.  The objects, however, are also represented in this database as “user” objects – which can be scripted.

After you script out the object, sys.sysdatabases you will see that the source of the data is the following:

Code block

FROM sys.databases d
LEFT JOIN sys.master_files$ f

There you have it – this view is predicated on the newer view called sys.databases.  As has been said over and over again, it only exists as a backwards compatibility view until Microsoft decides to remove it.  Also note that the other part of the foundation for the sysdatabases view is the sys.master_files$ view.  You can now see this in the system views collection inside the Resource database that we attached.  Unfortunately you can’t script it, but at least you can see the columns that it would return.  You also can’t query directly to that view – you have to reach it through the system views.