I need to be able to select the datatypes of all columns of a view. Since the Columns view does not provide this information (i.e. the ColumnType and other useful fields are null), I've seen two answers: The first suggests using "help columns <viewname>.*". The second suggests using "select type (<viewname>.<columnname>)".
The first is insufficient because I need a Select query that can be nested and whose columns can be manipulated. The second is insufficient because the select operates on only one column at a time.
Is there a way to select the datatypes of all columns of a view with one Select statement?
Why can't you select all column types in one select?
select top 1 type(col1),type(col2),...,type(coln) from view
But even this is not realy elegant...
Maybe the following would be an alternative
1. create a tmp table with no data from the view.
create table v_tmp as (select * from yourView) with no data.
2. retrieve the column types from dbc.tables
3. drop v_tmp table.
would overcome the issue that if business logic is applied to the view even a top 1 might take a long time to come back.
Thanks for the replies. I will give the temp table suggestion a try, but am open to any better alternatives. I have an additional requirement/constraint on my problem: I will be getting the metadata for all columns in all tables and views in all schemas. Thus creating and dropping a thousand temp tables, one for each view, is not ideal.
this might take a while to run but it is only one script and does not need to create and drop tables.
I used a volatile table for demo purposes only but you might want a permanent one....
.run file = /yourpath/logon.txt ;
.set width 500;
.OS rm /yourpath/view_col_type_sql.txt;
.export report file=/yourpath/view_col_type_sql.txt
select 'insert into view_column_data_type Select ''' !! Trim(databasename) !! ''','''!!Trim(tablename) !! ''','''!!Trim(columnname)!!''',type('!!trim(databasename)!! '.' !! trim(tablename)!! '.' !! trim(columnname) !!');'(title '')
where (databasename, tablename) in (select databasename, tablename from dbc.tables where tablekind = 'V')
create volatile table view_column_data_type
) primary index (databasename, tablename)
on commit preserve rows;
.run file /yourpath/view_col_type_sql.txt;
order by 1,2,3
I used the same approach for create table, store column info in table drop table.
so generate the statements and call the generated script.
Seems to run much faster.
I have the following CASE statement in my view:
WHEN 'A001' THEN 'SOMETHINGxxxxxxxx'
END) AS VARCHAR(17) ) AS EVNT_DESC
When I query the data dictionary and look for the columntype of CV, i get a datatype length of 34.
why is the length double what the view has declared?
All literals default to Unicode character set, thus the CASE results in a Unicode string.
dbc.ColumnsV returns the ColumnLength as physical size in bytes, for Unicode (CharType=2) you must divide by two to get the logical size.
If you want to get Latin you must apply
TRANSLATE( CASE .... USING UNICODE_TO_LATIN)