Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbthj
Posting Yak Master
143 Posts |
 Posted - 2007-11-07 : 14:15:32
|
| In previous versions we could query the system tables to get metadata.In SQL 2005 they seem to be hidden. They are there. You can query them, but I can't find them.Question: Is there any way to see them in SSMS?Yeah, I know you aren't supposed to, but hey, they are usefull and I like them.Microsoft has given us a confusing collection of alternatives for finding metadata;System views, Catalog Views, Dynamic Management Views, Compatibility Views, Information_schema views.I have tried to organize some thoughts on what these are and here is what I make of them.If any of this is in error, I would like some corrections. SYSTEM VIEW - the stuff below and more. Shows system information. ...CATALOG VIEW - is a system view on the system tables ...DYNAMIC MANAGEMENT VIEW - is a system view on system tables and various buffers for current status info. ...COMPATIBILITY VIEW - is a system view which shows ONLY 2000 information. It is there for compatibility. ...INFORMATION_SCHEMA VIEW - each database has it's own set. Holds db-level metadata.Hayden |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-11-07 : 14:19:07
|
| They aren't hidden, but perhaps you haven't found what you want yet. I've got a SQL Server 2005 system table map on my wall here. There are online versions of it out there. In fact, I'm pretty sure my blog has a link to the MS one. So why do you think they are hidden? Where have you looked? What are you looking for?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-07 : 23:25:17
|
| In fact, they are in every db now. You can find them under tables -> system tables in ssms when you connect to server with admin link. |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2007-11-08 : 11:26:48
|
| Nope. There's funny business somewhere.I open ssms using the sa account.I open a database.Click on tables.Click on system tables.What I get contains zero tables with a name like 'sys%'If I query sysobjects I see system tables named 'sys%'But they don't show up in the gui list.This is goofy too: if I look for the owner of sysobjectsI find it is 'sys'. There is no system table named sysobjectsowned by 'dbo'. But I can query dbo.sysobjects and get results.It is not a view. Where is it? What is it? How can I find it in ssms? How do I see the sys.sys% tables? |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-11-08 : 11:44:43
|
they are all views in 2005. you'll find them under views/system views in SSMS.
elsasoft.org |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2007-11-08 : 12:01:11
|
| I see a lot of stuff via Google that says views are in and tablesare gone.Here is a link to an article titled, "Say goodbye to system tables in SQL Server 2005".http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1089808,00.htmlReading this article, it sure sounds like the tables are squirrelled away somewhere andunavailable. I found a "map" of 2005 system views on Microsoft.com. I found nothing on the physical tables. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-08 : 15:45:55
|
| Admin link is not just logon with sa, need enable DAC and specify admin:instance_name as server name. |
|
|
|
|
|
|
|
|
2005 System Tables: Where are they