0

I have 70+ tables with each having 30+ fields. I was asked to find data type and data length for certain fields in these tables, I thought it would be helpful to have an output file and then just search through it but I am not sure how do I get descr to accept multiple tables and give me an output? 

descr table_name;

I have tried 

Select table_name, column_name, data_type
From all_tab_columns
where table_name in (NAME OF MY TABLES)

This works but for some reason, the output is a little off in terms of data_length and I need data length as it shows on when executing Descr.

Edit: I am working in SQL developer.

by off I mean in descr I get the outputs for A as datatype NUMBER(38) but in all_tab_columns it gives me data length 22

Qasim
  • 13
  • 4
  • Please specify your database becuase every system handles things different.Fro mysql works SELECT COLUMN_NAME, TABLE_NAME,table_schema FROM INFORMATION_SCHEMA.COLUMNS; – nbk Aug 30 '19 at 14:32
  • 1
    Can you please elaborate on "off in terms of data_length". The information in all_tab_columns should be 100% identical to that returned by `DESCR`. Do you have an example where they don't match? – Frank Schmitt Aug 30 '19 at 14:36
  • 1
    reading an output file would be easier/faster than just querying dba_tab_cols? if you want a report, SQL Developer has data dictionary reports/html schema docs that will give you what you're looking for – thatjeffsmith Aug 30 '19 at 14:39
  • [Related answer](https://stackoverflow.com/a/28581465/266304); you could look at [other columns](https://stackoverflow.com/a/57578654/266304) to recreate what `describe` shows for char/byte? – Alex Poole Aug 30 '19 at 14:53
  • I am using SQL developer from Oracle. and by off I mean in descr I get the outputs for A as datatype NUMBER(38) but in all_tab_columns it gives me data length 22 – Qasim Aug 30 '19 at 15:23
  • @Qasim - that is handled in the answer I linked to in my previous comment... – Alex Poole Aug 30 '19 at 15:36
  • yes @AlexPoole thanks! I went over it the only trouble I now have is when I run descr it's giving me NUMBER(38) but the result using all_tab_columns gives me NUMBER(22) – Qasim Aug 30 '19 at 15:39
  • 1
    That sounds like you are still using `data_length`, not `data_precision` (and `data_scale`)? Justin's answer also covers that distinction. (And my old answer shows a `NUMBER(38)` being reported properly.) – Alex Poole Aug 30 '19 at 15:41
  • If you have data type `NUMBER` and both scale and precision are null then it is unconstrained; but then `desc` shows it as `NUMBER` and not `NUMBER(38)` ? – Alex Poole Aug 30 '19 at 15:56
  • thanks, @alex very helpful of you, appreciate the help – Qasim Aug 30 '19 at 16:34

2 Answers2

2

If the precision and scale are both null then describe shows that column as a plain NUMBER, not as NUMBER(38). If describe shows the column as NUMBER(38) then the data dictionary shows it with precision 38 and scale 0. Unless, that is, you created it as NUMBER(*,0):

create table t42 (
  num number,
  num38 number(38),
  num5_2 number(5,2),
  num_3 number(*,3),
  num_0 number(*,0)
);

desc t42;

Name   Null? Type         
------ ----- ------------ 
NUM          NUMBER       
NUM38        NUMBER(38)   
NUM5_2       NUMBER(5,2)  
NUM_3        NUMBER(38,3) 
NUM_0        NUMBER(38)   

select column_name, data_length, data_precision, data_scale
from user_tab_columns
where table_name = 'T42';

COLUMN_NAME     DATA_LENGTH DATA_PRECISION DATA_SCALE
--------------- ----------- -------------- ----------
NUM                      22                          
NUM38                    22             38          0
NUM5_2                   22              5          2
NUM_3                    22                         3
NUM_0                    22                         0

For my NUM_0 column data_precision is null, but data_scale is not, so - as with NUM_3 - the precision is defaulted to the maximum of 38 when it is described.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
1

Can you define what is "off" in data_length? I assume that the issue is that for character columns you want the length in characters not bytes. If so, you'd want the char_length column instead. That's defined in terms of characters. data_length is defined in terms of bytes.

Based on the edit, your problem appears to be with numeric columns. You'd want the data_scale and the data_precision to tell you that a column was a number(38,0). data_length is the size of the column in bytes but you specify the precision of a number in terms of decimal digits so the two lengths should be different.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • by off I mean in descr I get the outputs for A as datatype NUMBER(38) but in all_tab_columns it gives me data length 22. char_length gives me 0 – Qasim Aug 30 '19 at 15:23
  • 1
    @Qasim - Updated. You want `data_scale` and `data_precision`. – Justin Cave Aug 30 '19 at 15:33
  • thank you for the help I am still having issues with my Descr giving me Number(38) whereas all_tab_colum gives me 22 data_scale and data_precision results are empty. – Qasim Aug 30 '19 at 15:46
  • 1
    @Qasim - A value of 22 is almost certainly coming from `data_length` not `data_scale`. It's helpful if you edit your question to include a reproducible script (i.e. a `create table` statement and then a query against `all_tab_columns` that shows the issue you're seeing that we can run on our local machine. Or a link to a liveSQL.oracle.com session that demonstrates the issue you're seeing). – Justin Cave Aug 30 '19 at 15:54
  • yes, you are right! :) I got it working thank you for your kind help. – Qasim Aug 30 '19 at 16:33