I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

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 | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: MsSqlSystemResource - SQL Server 2005's 5th system database

tank you. Interesting!
2/3/2010 12:16 PM | eda
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET