Oracle-L: RE: SQL query

From: Jack Silvey <jack_silvey_at_yahoo.com>
Date: Sat, 13 Jul 2002 08:18:23 -0800
Message-ID: <F001.00497413.20020713081823@fatcity.com>

Vandana,

Put this into a script, save to hard drive, and run from sqlplus using "@path\script":

SET ECHO OFF
accept table_name prompt "Enter the name of the Table :"
set heading on
set verify on
set newpage 0
ttitle 'Table Description - Space Definition' spool tfstbdsc.lst

btitle off
column nline newline
set pagesize 54
set linesize 78
set heading off
set embedded off
set verify off
accept report_comment char prompt 'Enter a comment to identify system: '
select 'Date - '||to_char(sysdate,'Day Ddth Month

YYYY     HH24:MI:SS'), 


	'At            -  '||'&&report_comment' nline, 

	'Username      -  '||USER  nline 

from sys.dual 
/

prompt
set embedded on

set heading on
set verify off
column ts format a30
column ta format a30
column clu format a30
column pcf format 99999999999990
column pcu format 99999999999990

column int format 99,999,999,990 
column mat format 99,999,999,990 
column inx format 99,999,999,990 
column nxt format 99,999,999,990 
column mix format 99,999,999,990 
column max format 99,999,999,990

column pci format 99999999999990

column num format 99,999,999,990 
column blo format 99,999,999,990 
column emp format 99,999,999,990 
column avg format 99,999,999,990 
column cha format 99,999,999,990 
column rln format 99,999,999,990

column hdg format a30 newline
set heading off

select 	'Table Name' hdg, 		TABLE_NAME 		ta, 
 	'Tablespace Name' hdg, 		TABLESPACE_NAME 	ts, 


	'Cluster Name' hdg, 		CLUSTER_NAME 		clu, 

	'% Free' hdg, 			PCT_FREE		pcf, 

	'% Used' hdg,			PCT_USED		pcu, 

	'Ini Trans' hdg, 		INI_TRANS		int, 

	'Max Trans' hdg, 		MAX_TRANS		mat, 

	'Initial Extent (K)' hdg, 	INITIAL_EXTENT/1024	inx, 

	'Next Extent (K)' hdg, 		NEXT_EXTENT/1024	nxt, 

	'Min Extents' hdg, 		MIN_EXTENTS		mix, 

	'Max Extents' hdg, 		MAX_EXTENTS		max, 

	'% Increase' hdg, 		PCT_INCREASE		pci, 

	'Number of Rows' hdg, 		NUM_ROWS		num, 

	'Number of Blocks' hdg, 	BLOCKS			blo, 

	'Number of Empty Blocks' hdg, 	EMPTY_BLOCKS		emp, 

	'Average Space' hdg, 		AVG_SPACE		avg, 

	'Chain Count' hdg, 		CHAIN_CNT		cha, 

	'Average Row Length' hdg, 	AVG_ROW_LEN 		rln

from all_tables
where TABLE_NAME=UPPER('&&table_name')
/
set heading on
set embedded off
column cn format a30 heading 'Column Name' column fo format a15 heading 'Type'
column nu format a8 heading 'Null'
column nds format 99,999,999 heading 'No Distinct' column dfl format 9999 heading 'Dflt Len' column dfv format a40 heading 'Default Value' ttitle 'Table Description - Column Definition'

select 	COLUMN_NAME cn, 
       	DATA_TYPE || 
	decode(DATA_TYPE, 
		'NUMBER', 
		    '('||to_char(DATA_PRECISION)||

decode(DATA_SCALE,0,'',','||to_char(DATA_SCALE))||')',

		'VARCHAR2',  
		    '('||to_char(DATA_LENGTH)||')', 
		'DATE','', 
		'Error') fo, 
	decode(NULLABLE,'Y','','NOT NULL') nu, 
	NUM_DISTINCT nds, 
	DEFAULT_LENGTH dfl, 
	DATA_DEFAULT dfv

FROM all_tab_columns
where TABLE_NAME=UPPER('&&table_name')
order by COLUMN_ID
/
ttitle 'Table Constraints'
set heading on
set verify off
column cn format a30 heading 'Primary Key' column cln format a45 heading 'Table.Column Name' column ct format a7 heading 'Type'
column st format a7 heading 'Status'
column ro format a30 heading 'Ref Owner|Constraint Name'
column se format a70 heading 'Criteria ' newline break on cn on st
set embedded on
prompt Primary Key
prompt

select 	cns.CONSTRAINT_NAME cn, 
	cns.TABLE_NAME||'.'||cls.COLUMN_NAME cln, 
       	initcap(cns.STATUS) st 
from 	all_constraints cns, 
	all_cons_columns cls 
where 	cns.table_name=upper('&&table_name') 
and 	cns.owner=user 
and 	cns.CONSTRAINT_TYPE='P' 
and 	cns.constraint_name=cls.constraint_name

order by cls.position
/
prompt Unique Key
prompt
column cn format a30 heading 'Unique Key'

select 	cns.CONSTRAINT_NAME cn, 
	cns.TABLE_NAME||'.'||cls.COLUMN_NAME cln, 
       	initcap(cns.STATUS) st 
from 	all_constraints cns, 
	all_cons_columns cls 
where 	cns.table_name=upper('&&table_name') 
and 	cns.owner=user 
and 	cns.CONSTRAINT_TYPE='U' 
and 	cns.constraint_name=cls.constraint_name

order by cls.position
/
column cln format a38 heading 'Foreign Key' newline column clfn format a38 heading 'Parent Key' break on cn on st skip 1
prompt Foreign Keys
prompt

select 	cns.CONSTRAINT_NAME cn, 
        initcap(STATUS) st, 
	cls.TABLE_NAME||'.'||cls.COLUMN_NAME cln, 
	clf.OWNER||'.'||clf.TABLE_NAME||'.'||clf.COLUMN_NAME
clfn 
from 	all_constraints cns, 
	all_cons_columns clf , 
	all_cons_columns cls 
where 	cns.table_name=upper('&&table_name') 
and 	cns.owner=user 
and 	cns.CONSTRAINT_TYPE='R' 
and 	cns.constraint_name=cls.constraint_name 
and     clf.CONSTRAINT_NAME = cns.R_CONSTRAINT_NAME 
and     clf.OWNER = cns.OWNER 
and     clf.POSITION = clf.POSITION

order by cns.CONSTRAINT_NAME, cls.position /
prompt Check Constraints
prompt
column se format a75 heading 'Criteria' set arraysize 1
set long 32000
select CONSTRAINT_NAME cn,

       initcap(STATUS) st, 
       SEARCH_CONDITION se

from all_constraints
where table_name=upper('&&table_name')
and owner=user
and CONSTRAINT_TYPE='C'
/
prompt View Constraints
select CONSTRAINT_NAME cn,

       initcap(STATUS) st, 
       SEARCH_CONDITION se

from all_constraints
where table_name=upper('&&table_name')
and owner=user
and CONSTRAINT_TYPE='V'
/
spool off
btitle off
ttitle off
clear breaks
clear columns
clear computes
set verify on
set long 80
set arraysize 30

Vandana <vandana_at_gdit.iiit.net> wrote: > > I am sorry for not being precise in my question. I > would like to > view the primary key, foreign key and other > 'constraints' information of > my table. While this could be viewed with 'desc' in > other dbmss, this > information is not provided with the 'desc' in > oracle. With what command > can I view it in Oracle? > > On Fri, 12 Jul 2002, Abdul Aleem wrote: > > > At SQL*Plus it is the same command > > Describe or desc and name of the table > > > > You also need to be connected to the database > > > > HTH > > Aleem > > -----Original Message----- > > Sent: Saturday, July 13, 2002 11:53 AM > > To: Multiple recipients of list ORACLE-L > > Subject: SQL query > > > > > > I am using an Oracle database running in Linux. I > would like to > > view the description of a table. For ex., if there > is a table called > > 'person'. I would like to see the names of the > columns in this table, > > their datatypes and such other details. In other > dbmss like mysql a > > command 'describe <tablename>' gives the > information. What is the > > equivalent in Oracle? > > > > > > -- > Please see the official ORACLE-L FAQ: > http://www.orafaq.com > -- > Author: Vandana > INET: vandana_at_gdit.iiit.net > > Fat City Network Services -- (858) 538-5051 FAX: > (858) 538-5051 > San Diego, California -- Public Internet > access / Mailing Lists >
> To REMOVE yourself from this mailing list, send an > E-Mail message > to: ListGuru_at_fatcity.com (note EXACT spelling of > 'ListGuru') and in > the message BODY, include a line containing: UNSUB > ORACLE-L > (or the name of mailing list you want to be removed > from). You may > also send the HELP command for other information > (like subscribing).

Do You Yahoo!?
Yahoo! Autos - Get free new car price quotes http://autos.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: jack_silvey_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sat Jul 13 2002 - 11:18:23 CDT