| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: less expensive way to fetch column names?
NetComrade wrote:
> > is there are a less expensive way to get column names from a table > then doing > SELECT LOWER(column_name) > FROM user_tab_columns > WHERE table_name ='my_table' > ORDER BY column_id > ....... > We use Oracle 8.1.7.4 on Solaris 2.7 boxes > remove NSPAM to email
(I've only got 9.2 here so your version may vary..)
user_tab_columns is based on user_tab_cols, which looks like
create or replace view USER_TAB_COLS
(TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_TYPE_MOD, DATA_TYPE_OWNER,
DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_ID,
DEFAULT_LENGTH, DATA_DEFAULT, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE,
DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED, SAMPLE_SIZE,
CHARACTER_SET_NAME, CHAR_COL_DECL_LENGTH,
GLOBAL_STATS, USER_STATS, AVG_COL_LEN, CHAR_LENGTH, CHAR_USED,
V80_FMT_IMAGE, DATA_UPGRADED, HIDDEN_COLUMN, VIRTUAL_COLUMN,
SEGMENT_COLUMN_ID, INTERNAL_COLUMN_ID)
as
c.name,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2',
'VARCHAR2'),
2, decode(c.scale, null,
decode(c.precision#, null, 'NUMBER',
'FLOAT'),
'NUMBER'),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR VARYING',
'VARCHAR'),
12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
58, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
105, 'MLSLABEL',
106, 'MLSLABEL',
111, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
122, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
123, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
178, 'TIME(' ||c.scale|| ')',
179, 'TIME(' ||c.scale|| ')' || ' WITH TIME
ZONE',
180, 'TIMESTAMP(' ||c.scale|| ')',
181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME
ZONE',
231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL
TIME ZONE',
182, 'INTERVAL YEAR(' ||c.precision#||') TO
MONTH',
183, 'INTERVAL DAY(' ||c.precision#||') TO
SECOND(' ||
c.scale || ')',
208, 'UROWID',
'UNDEFINED'),
decode(c.type#, 111, 'REF'),
nvl2(ac.synobj#, (select u.name from user$ u, obj$ o
where o.owner#=u.user# and o.obj#=ac.synobj#), ut.name),
c.length, c.precision#, c.scale,
decode(sign(c.null$),-1,'D', 0, 'Y', 'N'),
decode(c.col#, 0, to_number(null), c.col#), c.deflength,
c.default$, h.distcnt, h.lowval, h.hival, h.density, h.null_cnt,
decode(h.row_cnt, 0, 1, 1, 1, h.row_cnt-1), h.timestamp#,
h.sample_size,
decode(c.charsetform, 1, 'CHAR_CS',
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(c.charsetid),
4, 'ARG:'||c.charsetid),
decode(c.charsetid, 0, to_number(NULL),
nls_charset_decl_len(c.length, c.charsetid)),
decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
h.avgcln,
c.spare3,
decode(c.type#, 1, decode(bitand(c.property, 8388608), 0, 'B',
'C'),
96, decode(bitand(c.property, 8388608), 0, 'B',
'C'),
null),
decode(bitand(ac.flags, 128), 128, 'YES', 'NO'),
decode(o.status, 1, decode(bitand(ac.flags, 256), 256, 'NO',
'YES'),
decode(bitand(ac.flags, 2), 2, 'NO',
decode(bitand(ac.flags, 4), 4, 'NO',
decode(bitand(ac.flags, 8), 8,
'NO',
'N/A')))),
decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32,
'YES',
'NO')),
decode(c.property, 0, 'NO', decode(bitand(c.property, 8), 8,
'YES',
'NO')),
decode(c.segcol#, 0, to_number(null), c.segcol#), c.intcol#
from sys.col$ c, sys.obj$ o, sys.hist_head$ h, sys.coltype$ ac, sys.obj$
ot,
sys.user$ ut
where o.obj# = c.obj#
and o.owner# = userenv('SCHEMAID')
and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
and ac.toid = ot.oid$(+)
and ot.type#(+) = 13
and ot.owner# = ut.user#(+)
and (o.type# in (3, 4) /* cluster,
view */
or
(o.type# = 2 /* tables, excluding iot - overflow and nested
tables */
and
not exists (select null
from sys.tab$ t
where t.obj# = o.obj#
and (bitand(t.property, 512) = 512 or
bitand(t.property, 8192) = 8192))))
/
You could probably get some benefit by cutting out the stuff you are not interested in to get a more streamlined version. A quick look at the above and you could possibly get down to something like:
select o.name,
c.name
from sys.col$ c, sys.obj$ o
and (o.type# in (3, 4) /* cluster,
view */
or
(o.type# = 2 /* tables, excluding iot - overflow and nested
tables */
and
not exists (select null
from sys.tab$ t
where t.obj# = o.obj#
and (bitand(t.property, 512) = 512 or
bitand(t.property, 8192) = 8192))))
hth
connor
-- ------------------------------- Connor McDonald http://www.oracledba.co.uk Co-Author: "Mastering Oracle PL/SQL - Practical Solutions"Received on Mon Apr 05 2004 - 05:58:54 CDT
 |
 |