Working with Databases

Let's look at some basic operations you might want to do when interacting directly with your database server.

Listing Available Databases

To list those databases available to you when you first connect to a database server, use the following:

Server	Syntax
MySQL	SHOW DATABASES;
PostgreSQL	\l
Oracle	Individual Oracle server instances are centered on a particular database. This does not really make sense for this server.
Microsoft SQL Server	Set Database=master and execute sp_databases. The first column returned from sp_databases contains the name. The second column is the size of the database.

Listing Tables in a Database

To list tables available in the currently selected database (USE DatabaseName), use this:

Server	Syntax
MySQL	SHOW TABLES;
PostgreSQL	\dt
Oracle	SELECT * FROM all_tables | user_tables | dba_tables
Microsoft SQL Server	select * from sysobjects where type = 'u'

Describing a Table

To see the structure of a table, use this:

Server	Syntax
MySQL	DESCRIBE TableName;
PostgreSQL	\d TableName
Oracle	DESC TableName;
Microsoft SQL Server	SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE FROM pubs.INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'titles'