Query to display table structure

[skyem]

Hi.

I'm not actually sure if this is possible, but how can I write a query that will return the fields and their attributes of a table?

Thanks

 

[dickiebird]

Hi
Its probably allready written :
In Sybase you'd enter sp_help tablename
to retrieve the required info
I forget how Oracle does it - but someone else will no doubt
reply.
DB Dickie Bird
db@dickiebird.freeserve.co.uk

 

[bperry]

Try:

sp_help tablename


Also, another quick thing people do when you just need to see the column names, but not the attributes, is:

Select * from tablename where 1=2

Since 1 never = 2, no data ever gets returned, just the default column headings.

 

[gregsimpson]

DB2 has "DESCRIBE". I seem to remember ORACLE also has DESCRIBE.


db2 => describe table sysibm.sysindexes

Column Type Type
name schema name Length Scale Nul
------------------------------ --------- ------------------ -------- ----- ---
NAME SYSIBM VARCHAR 18 0 No

CREATOR SYSIBM VARCHAR 128 0 No

TBNAME SYSIBM VARCHAR 128 0 No

TBCREATOR SYSIBM VARCHAR 128 0 No

COLNAMES SYSIBM VARCHAR 640 0 No

UNIQUERULE SYSIBM CHARACTER 1 0 No

COLCOUNT SYSIBM SMALLINT 2 0 No

IID SYSIBM SMALLINT 2 0 No

NLEAF SYSIBM INTEGER 4 0 No

NLEVELS SYSIBM SMALLINT 2 0 No

FIRSTKEYCARD SYSIBM BIGINT 8 0 No

FULLKEYCARD SYSIBM BIGINT 8 0 No

CLUSTERRATIO SYSIBM SMALLINT 2 0 No

CLUSTERFACTOR SYSIBM DOUBLE 8 0 No

USER_DEFINED SYSIBM SMALLINT 2 0 No

SYSTEM_REQUIRED SYSIBM SMALLINT 2 0 No

CREATE_TIME SYSIBM TIMESTAMP 10 0 No

STATS_TIME SYSIBM TIMESTAMP 10 0 Yes

PAGE_FETCH_PAIRS SYSIBM VARCHAR 254 0 No

REMARKS SYSIBM VARCHAR 254 0 Yes

DEFINER SYSIBM VARCHAR 128 0 No

MADE_UNIQUE SYSIBM CHARACTER 1 0 No

SEQUENTIAL_PAGES SYSIBM INTEGER 4 0 No

DENSITY SYSIBM INTEGER 4 0 No

FIRST2KEYCARD SYSIBM BIGINT 8 0 No

FIRST3KEYCARD SYSIBM BIGINT 8 0 No

FIRST4KEYCARD SYSIBM BIGINT 8 0 No

PCTFREE SYSIBM SMALLINT 2 0 No

UNIQUE_COLCOUNT SYSIBM SMALLINT 2 0 No

INDEXTYPE SYSIBM CHARACTER 4 0 No

ENTRYTYPE SYSIBM CHARACTER 1 0 No

MINPCTUSED SYSIBM SMALLINT 2 0 No

REVERSE_SCANS SYSIBM CHARACTER 1 0 No

IESCHEMA SYSIBM VARCHAR 128 0 Yes

IENAME SYSIBM VARCHAR 18 0 Yes

IEARGUMENTS SYSIBM CLOB 32768 0 Yes

INDEX_DESC SYSIBM BLOB 1048576 0 Yes

INTERNAL_FORMAT SYSIBM SMALLINT 2 0 No


38 record(s) selected.

db2 =>

 

[crufty]

Just to confirm:

Oracle uses 'describe' to give basic table information

describe table (or, if you want to save 3 letters of typing in SQLPlus, descr table)

obscure useless Oracle trivia:
You do NOT need to end the line with a semicolon for the describe command in Or.acle