Using result of HELP column within a query

Database
Enthusiast

Using result of HELP column within a query

Hi,

while searching for a possibility to get the datatype of the columns of a view I came

HELP COLUMN dbname.viewname.*

That gives me in SQL Assistant the result that I need. But is there a possibility to use this result in a SELECT or MACRO or STORED Procedure?

Any help or suggestions are appreciated!

Thanks in advance an best regards

Helmut

P.S. I know the possibility of using

SELECT TYPE(column1), TYPE(column2)
FROM dbname.viewname

But for using this, the view has to contain data. But in my case I cannot guarantee that...

2 REPLIES

Re: Using result of HELP column within a query

You could use BTEQ to export the result of the HELP statement and then use the exported data in your SELECT statement within BTEQ using shell script or batch script.

Supporter

Re: Using result of HELP column within a query

not nice but might be sufficient for you...

1. create a table where you store the results.

2. generate per view 3 statements which

a. create a table dummy as select * from your view

b. insert select the dbc.columns info into your table from 1

c. drop the table

3. run the generated code

my test on DBC run into a  [3933] The Maximum Possible Row Length in the Table is too Large

- so  it will not work always...You can change this into a column approach but this would mean much more statements to generate and execute...

below the code - you need to replace YourDB with one DB where you have create & drop rights. It also assume that your user has appropriate rights on the views.

Ulrich

create table YourDB.view_columns
as
(
select * from dbc.columns
) with no data
primary index (databasename, tablename);

select '
create table YourDB.dummy
as (
select * from ' !! trim(databasename) !! '.' !! trim(tablename) !!'
) with no data
no primary index;

insert into YourDB.view_columns
select '''!! trim(databasename) !! ''',
''' !! trim(tablename) !!''',
ColumnName ,
ColumnFormat ,
ColumnTitle ,
SPParameterType ,
ColumnType ,
ColumnUDTName ,
ColumnLength ,
DefaultValue ,
Nullable ,
CommentString ,
DecimalTotalDigits ,
DecimalFractionalDigits ,
ColumnId ,
UpperCaseFlag ,
Compressible ,
CompressValue ,
ColumnConstraint ,
ConstraintCount ,
CreatorName ,
CreateTimeStamp ,
LastAlterName ,
LastAlterTimeStamp ,
CharType ,
IdColType ,
AccessCount ,
LastAccessTimeStamp ,
CompressValueList ,
TimeDimension ,
VTCheckType ,
TTCheckType ,
ConstraintId ,
ArrayColNumberOfDimensions ,
ArrayColScope ,
ArrayColElementType ,
ArrayColElementUdtName
from dbc.columns
where databasename = ''YourDB''
and tablename = ''dummy'';

drop table YourDB.dummy;
'
from dbc.tables
where databasename = 'dbc'
and tablekind = 'V'
order by 1
;