All Forums Database
HelmutR 6 posts Joined 01/13
04 Nov 2014
Using result of HELP column within a query

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
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...

akmondal88 3 posts Joined 03/13
04 Nov 2014

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.


ulrich 816 posts Joined 09/09
04 Nov 2014

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.

create table YourDB.view_columns
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           ,
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


feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

You must sign in to leave a comment.