System catalog tables?

[php newbie]

Hello,

I am relatively new to SQL Server, although I have used Oracle
extensively.

In Oracle, there are system tables that you can query in order to get
a list of all schemas and all the tables in them. Is there a similar
concept in SQLServer? How would I find about the system dictionary
tables?

TIA :-)

[Erland Sommarskog]

php newbie (ne**********@yahoo.com) writes:

I am relatively new to SQL Server, although I have used Oracle
extensively.

In Oracle, there are system tables that you can query in order to get
a list of all schemas and all the tables in them. Is there a similar
concept in SQLServer? How would I find about the system dictionary
tables?

Yes, there are. They are documented in Books Online. Beware that not
all tables are documented, and not all columns in the documented tables
are docuemented. You should stick to the documented tables and columns.

In the next version of SQL Server, Microsoft is reworking how they
store metadata, and there will be a new interface for system data.
The current system tables will remain as legacy views.

Note also that Microsoft offers a set of property functions through
which you can retrieve information about various objects. There
are objectproperty(), indexproperty(), databasepropertyex() to name
a few. Again, look in Books Online.

Finally, MS SQL Server implements the INFORMATTION_SCHEMA views which
are ANSI standard for metadata. Many people are fond of these, I am not.
The problem with them is that they are incomplete, so you still need
to query system tables anyway. They are good for portability though.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Brian Peasland]

php newbie wrote:

Hello,

I am relatively new to SQL Server, although I have used Oracle
extensively.

In Oracle, there are system tables that you can query in order to get
a list of all schemas and all the tables in them. Is there a similar
concept in SQLServer? How would I find about the system dictionary
tables?

TIA :-)

If you are used to Oracle, then you are used to the DBA_* views, etc.
SQL Server does have data dictionary tables, but they are different.
First, each database in SQL Server has data dictionary tables (they
start with "sys"). You can look up each view in Books OnLine. For
instance, in Query Analyzer to get a list of all tables, use the
following query:

use Northwind
go
SELECT name FROM sysobjects WHERE xtype='U'

That data dictionary query will only give you the tables in the
Northwind database. Each database has similar tables.

The "master" database also has data dictionary tables. These tables are
from a instance-wide perspective. So if you need instance-wide
information, look here.

HTH,
Brian

--
================================================== =================

Brian Peasland
dba@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.
"I can give it to you cheap, quick, and good. Now pick two out of
the three"