I want some Moore

MsSqlSystemResource - SQL Server 2005's 5th system database

MsSqlSystemResource is a database that complements the master db. It is like the name smartly imples a resource database.

All system stored procedures, views and functions are stored here.

This databse is by all means hidden from the user. You can't view it in Object Explorer nor with the use of sp_helpDb or selecting from a sys.databases view.

So how do we know it's even there? Pretty simple. Go to the Data directory of your SQL installation where the databases are put by default and

there you'll see MsSqlSystemResource.mdf and MsSqlSystemResource.ldf.

The reason for this db is in my opinion probably in being able to easily upgrade the SQL Server with service packs.

just replace the resource database and that's it.

If you want to see what is in there there is of course a way:

Stop the SQL Server service, copy both files, rename the copies to MsSqlSystemResource1.mdf and MsSqlSystemResource1.ldf.

Start the server and attach the new files with this script generated by the SSMS:

USE [master]
GO
CREATE DATABASE [mssqlsystemresource1] ON 
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource1.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mssqlsystemresource1.ldf' )
 FOR ATTACH
GO
if not exists (select name from master.sys.databases sd where name = N'mssqlsystemresource1' AND
    SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() ) 
        EXEC [mssqlsystemresource1].dbo.sp_changedbowner @loginame=N'yourLoginAccount', @map=false
GO

Change your path according to your install and change yourLoginAccount to your login.

Now you can browse and script system objects. Here are 2 just for fun:

create function [sys].[dm_db_index_physical_stats]
(
    @DatabaseId            SMALLINT        = 0,
    @ObjectId              INT             = 0,
    @IndexId             INT               = -1,
    @PartitionNumber    INT                = 0,
    @Mode                 nvarchar(20)      = NULL
)
returns table
as
    return select * from OpenRowset
        (
            IndexAnalysis,
            @DatabaseId,
            @ObjectId,
            @IndexId,
            @PartitionNumber,
            @Mode
        )

CREATE VIEW [sys].[dm_exec_query_stats] AS
    SELECT *
    FROM OpenRowset (TABLE QUERY_STATS)

You can see that it uses a special form of OpenRowset that selects from internal objects that aren't accessible to us.

But it's always fun to find something new about your tool isn't it :)

Of course you can get the bodies of every object with

SELECT object_definition(OBJECT_ID('schema.objectName'))

But it's COOLNESS factor is lower than MsSqlSystemResource's one :))

in our case this would be:

SELECT object_definition(OBJECT_ID('sys.dm_exec_query_stats'))

SELECT object_definition(OBJECT_ID('sys.dm_db_index_physical_stats'))

Print | posted on Monday, March 12, 2007 4:34 PM