Master database tables

Hello everybody,

One thing that I observed is sometimes while creating a database, in the master database I find a whole set of system table and sometimes they are just few tables of them that showup, in particular dbo.MSreplication_options, dbo.spt_fallback_db, dbo.spt_fallback_dev, dbo.spt_fallback_usg,dbo.spt_monitor, dbo.spt_values. Is there any setting that exists to have all of them seen.
. The first time i saw this was so terrified, then my sr.DBA said it is ok it have it like that. I am still not sure why was this happening. Now another thing, is I am actually seeing all the system views. Question is how is that we are having views and without having the physical tables in the database. I want to make sure that I am on right direction as I have to take complete ownership of an instance and I want to get my thoughts cleared.

Thanks all.

more ▼

asked Apr 29, 2010 at 11:41 AM in Default

avatar image

Katie 1
1.4k 132 164 205

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I wouldn't sweat it. The real system tables on 2005 and 2008 servers are hidden behind several layers. So it's not a surprise that you're not seeing them listed. Even if you do see them listed, what you're seeing are not really system tables anyway, they're special views that are functioning like the system tables used to in 2000.

more ▼

answered Apr 29, 2010 at 11:50 AM

avatar image

Grant Fritchey ♦♦
137k 20 47 81

  • An interesting exercise is to script out the system views (you can do it with SMO or any non-SSMS IDE (I think)) - have a look at the source of them and you'll quickly realise that they depend on whole reams of stuff that you have never heard of.

Apr 29, 2010 at 12:15 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Apr 29, 2010 at 11:41 AM

Seen: 3246 times

Last Updated: Apr 29, 2010 at 12:50 PM

Copyright 2018 Redgate Software. Privacy Policy