databases and tables information showing.

[jenniferyiu]

Hello !!

May I ask what's the SQL command in mssql to show all the databases
in the sql server ?

besides it'd be great if someone can tell the command for showing the
tables from a database.

I know the answers for the above for mysql that's

show databases;

show tables from thisonedatabase;

but I don't know what're the answsers in the case of mssql.

Could someone please tell about it ?

Thanks a lot for all these help !!

@: >
Jennifer Yiu

[BP Margolin]

Jennifer,

To list all databases:

exec sp_helpdb

To list all database objects (including system objects) in a database:

exec sp_help

To list only user tables in a database, you can use the ANSI SQL standard
INFORMATION_SCHEMA views, along with the OBJECTPROPERTY metadata function to
screen out the system and pseudo-system tables:

select TABLE_NAME
from INFORMATION_SCHEMA.TABLEs
where TABLE_TYPE = 'BASE TABLE'
and objectproperty(object_id(TABLE_NAME), 'IsMSShipped') = 0

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.

"jenniferyiu" <jennife...@yahoo.com> wrote in message
news:26e2e5cb.0212...@posting.google.com...

[John Bell]

Hi

Check out sp_helpdb in Books online and also the INFORMATION_SCHEMA views that
are the ansi standard ways to information about a database schema.

John