SQL description of table? 2

[ThatRickGuy]

Hi guys,
I'm trying to find a way to get a SQL statement to return a description of a table. This is coming through ADO.Net, so just using DESC <tablename> isn't going to work.

I've tried:

select dbms_metadata.get_ddl( 'TABLE', 'ANSWER', 'HTSCME' ) from dual;

and

select column_name,data_type from user_tab_columns where table_name = 'HTSCME.ANSWER';

But both are returning 0 rows. With the user_tab_columns query, even if I take off the where clause I still get no rows returned. Could this be a permissions issue? Or am I barking up the wrong tree?

Thanks!

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[cmmrfrds]

It could be permissions. Instead of the table, use the views in the sys schema. There are many views starting with all_

For example.
sys.all_table
sys.all_indexes
sys.all_tab_comments
sys.all_tab_columns
etc.........

 

[ThatRickGuy]

Excellent! Thanks cmmrfrds, this worked perfectly:

SELECT * FROM SYS.ALL_TAB_COLS WHERE Table_Name = 'ANSWER';

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ThatRickGuy]

Hmm, I bumped into something interesting.

When I originally created the tables, I selected the datatype Number for a bunch of columns that should have been Integer. After realising my mistake, I went back and altered the tables to have the columns as Integers. But now when I hit the All_Tab_Col view, it is still listing the columns as Number instead of Integer. Is the a stored proc I have to run to update that view or something?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[SantaMufasa]

Rick,

Perhaps you will find useful my scripts that do just as you wanted: DESCRIBE <table>, plus it lists all of the constraints for each column along with table constraints. You run the "strumake.sql" script one time only for a schema, then you can run the "stru.sql" script anytime you want the full disclosure for any of your tables.

Script 1: "strumake.sql" (one-time only):

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. Neither the 
REM author nor Dasages, LLC, makes any warranty regarding this 
REM script's fitness for any industrial application or purpose nor is 
REM there any claim that this or any similarly-distributed scripts 
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
drop table temp_cons;
create table temp_cons as
select constraint_name, constraint_type, ' ' search_condition,
r_constraint_name, status
from user_constraints
where 1 = 2;
alter table temp_cons modify search_condition varchar(4000)
/
create or replace procedure prep_user_constraints (table_2_do in varchar)
is
	my_search_condition varchar(32767);
begin
	delete temp_cons;
	commit;
	for i in (select constraint_name
			,constraint_type
			,search_condition
			,r_constraint_name
			,status
            	    from user_constraints
            	   where table_name = upper(table_2_do)) loop
		my_search_condition := i.search_condition;
		insert into temp_cons values
			(i.constraint_name
			,i.constraint_type
			,my_search_condition
			,i.r_constraint_name
			,i.status);
		commit;
	end loop;
end;
/

Script 2: "stru.sql":

REM **************************************************************
REM David L. Hunt (file author) distributes this and other 
REM files/scripts for educational purposes only, to illustrate the 
REM use or application of various computing techniques. The author
REM makes no warranty regarding this script's fitness for any 
REM industrial application or purpose nor is there any claim that
REM this or any similarly-distributed scripts are error free or
REM should be used for any purpose other than illustration.
REM **************************************************************
set echo off
set verify off
set feedback off
set linesize 100
set pagesize 500
break on "#" on a on b
accept x prompt "Enter table name: "
col a format a18 heading "Column|Name" word_wrapped
col b format a13 heading "Data Type|and Length"
col c format a43 heading "[Constraint Type: Name: En-/Dis-abled]|and Enforcement" word_wrapped
col "#" format 99 heading "Col| # "
exec prep_user_constraints('&x')
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
       '[' ||
           decode(con.constraint_type,'P','PK','R','FK','C','CK','U','UK')
           || ':' ||con.constraint_name
           || ':' ||con.status
           || '] ' ||
           decode(con.constraint_type,
              'P','NOT NULL/UNIQUE',
              'U','UNIQUE',
              'C',con.search_condition,
              'R','Match : '||r_constraint_name) c
from   temp_cons con,
       user_cons_columns con_col,
       user_tab_columns col
where  col.table_name      = upper('&x')
  and  con.constraint_name = con_col.constraint_name
  and  col.table_name      = con_col.table_name
  and  col.column_name     = con_col.column_name
  )
union
((select column_id "#",
         col.column_name a,
         col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col
 where col.table_name = upper('&x'))
minus
(select column_id "#",
        col.column_name a,
        col.data_type ||
           decode(substr(data_type,1,4),
             'DATE',null,
             'LONG',null,
             'VARC','('||ltrim(to_char(data_length,'9999'))||')',
             'CHAR','('||ltrim(to_char(data_length,'999'))||')',
             'FLOA','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'9999')))||')'
                   ,null,
             'NUMB','('||ltrim(to_char(data_precision,'99'))||
                    decode(data_scale,
                      0,null,
                      ','||ltrim(to_char(data_scale,'999')))||')'
                   ,null)
           b,
        ' ' c
 from user_tab_columns col, user_cons_columns con_col
 where col.table_name  = upper('&x')
   and col.table_name  = con_col.table_name
   and col.column_name = con_col.column_name)
)
order by 1
/
accept again prompt "Another table (Y/N) ? "
set heading off
set pagesize 0
spool temp.sql
set termout off
select decode(upper('&again'),'Y','@stru') from dual;
set termout on
spool off
set heading on
set pagesize 23
set feedback on
@temp

Sample output from "stru.sql" invocation:

SQL> @stru
Enter table name: s_emp

Col Column             Data Type     [Constraint Type: Name: En-/Dis-abled]
 #  Name               and Length    and Enforcement
--- ------------------ ------------- -------------------------------------------
  1 ID                 NUMBER        [CK:S_EMP_ID_NN:ENABLED] "ID" IS NOT NULL
                                     [PK:S_EMP_ID_PK:ENABLED] NOT NULL/UNIQUE
  2 LAST_NAME          VARCHAR2(25)  [CK:S_EMP_LAST_NAME_NN:ENABLED] "LAST_NAME"
                                     IS NOT NULL

  3 FIRST_NAME         VARCHAR2(25)
  4 USERID             VARCHAR2(8)   [UK:S_EMP_USERID_UK:ENABLED] UNIQUE
  5 START_DATE         DATE
  6 COMMENTS           VARCHAR2(255)
  7 MANAGER_ID         NUMBER        [FK:S_EMP_MANAGER_ID_FK:ENABLED] Match :
                                     S_EMP_ID_PK

  8 TITLE              VARCHAR2(25)  [FK:S_EMP_TITLE_FK:ENABLED] Match :
                                     S_TITLE_TITLE_PK

  9 DEPT_ID            NUMBER        [FK:S_EMP_DEPT_ID_FK:ENABLED] Match :
                                     S_DEPT_ID_PK

 10 SALARY             NUMBER
 11 COMMISSION_PCT     NUMBER        [CK:S_EMP_COMMISSION_PCT_CK:ENABLED]
                                     commission_pct IN (10, 12.5, 15, 17.5, 20)

Another table (Y/N) ?
********************************************************************************

Let us know if this is useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Beilstwh]

The reason that

select column_name,data_type from user_tab_columns where table_name = 'HTSCME.ANSWER';

didn't work it that the table_name column does NOT contain the schema name. The following would have worked

select column_name,data_type from ALL_tab_columns where table_name = 'ANSWER'
and owner='HTSCME';

Bill
Oracle DBA/Developer
New York State, USA

 

[ThatRickGuy]

Heya Dave,
That actually looks like exactly what I need. But I'm having the same problem. It is still returning the data types of the columns from when they were originally created.

I supose I can just grab the scripts from Toad, drop them all and re-create them. It's just a hassle.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ThatRickGuy]

Or maybe not. Dropped and recreated all of the tables, and even though the desc tablename is showing Integer, the all_tab_col is still spitting out Number. Same with your scripts Dave.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[SantaMufasa]

Rick,

That is because Oracle supports only NUMBER as the actual data type for ANSI SQL's INTEGER, REAL, FLOAT, et cetera, numeric data types. Bottom line: Oracle stores all numbers using the same internal data representation.

Sorry,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

Rick,

that's not a problem. I create all my tables with integers, because I really do mean integers.

Unfortunately, Oracle automatically "translates" this into a number (38,0), i.e. a 38 digit number with no decimal places.

Your script (and mine too) can say integer from now until the end of time, but Oracle interprets and stores such things as numbers. It's an irritating foible, since I believe that integers should be supported as a data type. If the DBA explicitly says integer, then guess what O mighty Oracle database engine, just for once in a while, he might really mean a *************** integer.

As you may have noticed by now, this really doesn't bother me in the slightest, and I'm totally chilled out about it. However, don't have conniptions (to use a blatant Americanism) about it. Just go with the Oracle flow man and chill.......

T

Grinding away at things Oracular

 

[SantaMufasa]

And when John says...

Just go with the Oracle flow man and chill.......

...he means it with utmost respect, love, and brotherhood.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[cmmrfrds]

Doesn't the precision and scale kind of tell whether you defined as integer or not. Anyway, this is how I viewed it.

select column_name,
(case when data_type = 'VARCHAR2' then
data_type || '(' || data_length || ')'
when data_type = 'NUMBER' then
data_type || '(' || data_precision || ',' || data_scale || ')'
else data_type end) as dat_type,
(case when nullable = 'N' then
'NOT NULL'
else '' end) as nullokay
from sys.all_tab_columns where table_name = 'RVU_STAGING_CLARITY' and owner = 'RVU'
Order by column_name

 

[SantaMufasa]

Also, Rick, part of the reason your original code,

select dbms_metadata.get_ddl( 'TABLE', 'ANSWER', 'HTSCME' ) from dual;

...didn't work is because you goobered up the invocation. If I am not mistaken, from the context of your follow-up information, your invocation should have been:

select dbms_metadata.get_ddl( 'TABLE', 'HTSCME', 'ANSWER') from dual;

...The invocation format for "get_ddl" is:

dbms_metadata.get_ddl( '<object type>', '<owner>', '<object name>')

Let us know if this gives you the results for which you were hoping.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ThatRickGuy]

Yeah, I just saw that in your script. I guess I can check the precision in a decode and return 'Integer'.

<rant>
Holy sweet jebus in a half shell. Oracle has got to be the most retarded database system I've had to work with. I've had a few year off from it. I started out with it years and years ago, and it was a bit rough around the edges, but it was the best of the best at the time. But after 6 years of not working with it seriously, all I can say is... what a piece of crap. 6 years ago MS:Access sucked too, it still does, but at least it has improved in the last 6 years.

I mean, I'd recommend people use Sybase before dealing with this crap. Heck, non-relational flat files or even Excel as a database (bad item TM) would give a more consistent form of behavior than this.
</rant>

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[SantaMufasa]

Rick,

I can tell that you must have just finished reading "How to Win Friends & Influence People" by Dale Carnegie. <grin>


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

A star for succinct sarcasm you old reprobate.

Ner!

Grinding away at things Oracular

 

[ThatRickGuy]

select dbms_metadata.get_ddl( 'TABLE', 'HTSCME', 'ANSWER') from dual;

Returns: ORA-31603: object "HTSCME" of type TABLE not found in schema "ANSWER"

select dbms_metadata.get_ddl( 'TABLE', 'ANSWER', 'HTSCME') from dual;

Returns no data. Presumably because it is only looking at the Users tablespace.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[Thargy]

Rick,

if you want, I have a script that uses dbms_metadata to extract what I would call "clean" DDL from a database.

If you wish, you are most welcome to it, but it is too large to post here.

We can communicate via SantaMufasa, who has my E-Mail address, if you need.
N.B. I offer this free of charge, but obviously with no guarantees.

Regards

T

Grinding away at things Oracular

 

[SantaMufasa]

...you old reprobate.

Nay, Nay, mate. My parents always taught me that reprobation caused hair growth on the palms of one's hands. <sneaky grin>


...And yes, Rick, I can put you in contact with John (Tharg) for his script if you reach me via my signature.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ThatRickGuy]

Hey Guys, I think I finally have what I am looking for. I got it wrapped up into a package so I can call it from my existing data layer. I check on client side to see if the data type is number and the Data Precision is -1, if it is, then it is (should be) an Integer.

Who ever decided that the Integer data type should be stored as a Decimal with a null precision... gah...

CREATE OR REPLACE PACKAGE BODY HTSCME.PAK_MYDESC AS 

PROCEDURE usp_Select (p_results_cursor OUT CURSOR_TYPE, p_TableName IN VARCHAR2, p_Schema IN VARCHAR2) AS 
 BEGIN
 HTSCME.prep_user_constraints (p_TableName, p_Schema);
 
     OPEN p_results_cursor FOR
     SELECT * FROM 
(
select column_id,
        col.column_name ColumnName,
        col.data_type DataType,
        NVL(col.data_precision,-1) DataPrecision,
       '[' ||
           decode(con.constraint_type,'P','PK','R','FK','C','CK','U','UK')
           || ':' ||con.constraint_name
           || ':' ||con.status
           || '] ' ||
           decode(con.constraint_type,
              'P','NOT NULL/UNIQUE',
              'U','UNIQUE',
              'C',con.search_condition,
              'R','Match : '||r_constraint_name) Constraint
from   htscme.temp_cons con,
       all_cons_columns con_col,
       all_tab_columns col
where  col.table_name      = upper(p_TableName)
  and  col.owner           = upper(p_Schema)
  and  con.constraint_name = con_col.constraint_name
  and  col.table_name      = con_col.table_name
  and  col.column_name     = con_col.column_name
  
union

select  column_id,
         col.column_name ColumnName,
         col.data_type DataType,
         NVL(col.data_precision,-1) DataPrecision,
        ' ' Constraint
 from all_tab_columns col
 where col.table_name = upper(p_TableName)
  and  col.owner      = upper(p_Schema)

minus

select column_id,
        col.column_name ColumnName,
        col.data_type DataType,
        NVL(col.data_precision,-1) DataPrecision,
       ' ' Constraint
from   htscme.temp_cons con,
       all_cons_columns con_col,
       all_tab_columns col
where  col.table_name      = upper(p_TableName)
  and  col.owner           = upper(p_Schema)
  and  con.constraint_name = con_col.constraint_name
  and  col.table_name      = con_col.table_name
  and  col.column_name     = con_col.column_name

)
order by 1;
 END;
 

END;
/

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.