question

Katie 1 avatar image
Katie 1 asked

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.

sql-server-2008sql-server-2005administrationsystem-databases
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered

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.

1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
+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.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.