views:

31

answers:

1

Subquestioning [1] and [2].
Where does SQL Server store server objects?

And why there?

Update:
This question is defined as subquestion and in context of cited questions...

Should I understand the answer that sql_policy_trigger, shown under Server Objects\Triggers in Object Explorer of SSMS (MS SQL Server R2), is stored in master database?


=====Update2:
Please give me the references to msdn docs in your answer(s).

It is not obvious that system or server configuration objects, meta-information or catalogs are stored in database(s). For ex., FTS catalog is not stored in database but in file (system), etc.

[3] (BOL2005-2008R2) tells that:

  • "master Database

    Records all the system-level information for an instance of SQL Server"

    • "In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database"

So, it is instance-wide (but not server-wide) and it clearly mentions "system".
As well as it hints that "they" are not stored in master database.

Also, I would like to understand why SSMS permits to script server objects (for ex., right-click in Object Explorer under Server Objects --> Triggers on syspolicy_server_trigger --> Script Server Trigger as...---> ) while, for ex., system views do not have such possibility.


Cited:
[1] Where does a Server trigger save in SQL Server ?
http://stackoverflow.com/questions/3843256/where-does-a-server-trigger-save-in-sql-server
[2]
“system objects” vs. “server objects” in SQL Server - terms definitions?
http://stackoverflow.com/questions/3845035/system-objects-vs-server-objects-in-sql-server-terms-definitions
[3]
master Database
[3a]
(SQL Server 2008 R2 Books Online)
http://msdn.microsoft.com/en-us/library/ms187837.aspx
[3b]
(SQL Server 2005 Books Online)
http://msdn.microsoft.com/en-us/library/ms187837(v=SQL.90).aspx

+4  A: 

sys.objects, sys.indexes etc (as per your trigger question). See Object catalog views in MSDN

As for why, a relational database may as well use a relation structure to store information about itself, no? Apart from a few registry settings used on start up, almost every setting and object is stored in a table somewhere in SQL Server.

Examples:

Edit, I had to dredge my memory about why this is so...

Rule zero in Codd's law

The system must qualify as relational, as a database, and as a management system. For a system to qualify as a relational database management system (RDBMS), that system must use its relational facilities (exclusively) to manage the database.

This ignores the "relational" puritans and zealots but explains why sys.objects etc are used.

Edit:

Making a guess at what OP really wants... server level objects are stored in the master database.

gbn
It looks to me like you referenced views and database-wide (or database specific) ones while I asked about storage location of objects and server-wide ones.
vgv8
Did you not read my answer or any of the links? There are catalog for *all* objects (sys.server_principals, sys.configurations etc). Otherwise, please clarify your question so we can answer what you really want to know. Don't criticise those who try to answer vague, ill-defined questions.
gbn