x

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

Katie 1 gravatar image

Katie 1
1.4k 132 163 202

(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

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

+1 - 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 ♦♦
P.s. when I say you I mean the OP - not you Grant!
Apr 29, 2010 at 12:16 PM Matt Whitfield ♦♦
Why not. I've only looked at a few of them.
Apr 29, 2010 at 12:18 PM Grant Fritchey ♦♦
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1945
x1834
x126
x19

asked: Apr 29, 2010 at 11:41 AM

Seen: 2671 times

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