Posted by Erland Sommarskog on 03/06/07 22:42

TGEAR (ted_gear@hotmail.com) writes:
> I am using MS SQL Server management studio.
> I treid to see some system tables which are sysobjects, syscolumns,
> systypes, etc.., but i don't see the list under the system tables
> folder. There is one table showing, sysdiagrams; however, I was able
> to query sql stmts though.
> where are they located? or Do I need to contact a dba to release those
> tables? thanks.

There are no such tables in SQL 2005. In SQL 2005 there is no access to
the system tables. Instead there are a number of views that exposes this
information. There are four sets of them:

o Catalog views. These are new views that are better structured, have less
funny reserved columns etc.
o Dynamic Management Views: Views corresponding to the old virtual tables
in SQL 2000, like sysprocesses. There is a whole bunch of them. There
is a *lot* more information exposed now.
o Compatibility views. These views have the same schema and contents as
the old system tables. Beware that columns that previously were
undocumented typically now only have NULL or 0.
o INFORMATION_SCHEMA. The same as in SQL 2000.

You need also to be aware of that there is a change in security policy for
SQL 2005, so that users no longer can see all object by default. Typically,
if you look in sysobjects, you would only see the object you have been
granted access to.

Books Onlione hss more information.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]