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'
|
| 