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'))
|
Legacy Comments
eda
2010-02-03 |
re: MsSqlSystemResource - SQL Server 2005's 5th system database tank you. Interesting! |