Datatype of a Column of a View

Database

Datatype of a Column of a View

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?

8 REPLIES
N/A

Re: Datatype of a Column of a View

Why can't you select all column types in one select?

something like

select top 1 type(col1),type(col2),...,type(coln) from view

But even this is not realy elegant...

N/A

Re: Datatype of a Column of a View

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.

Re: Datatype of a Column of a View

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. 

N/A

Re: Datatype of a Column of a View

a Stored Procedure might help - but agree that it is not ideal

N/A

Re: Datatype of a Column of a View

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 '')
from dbc.columns
where (databasename, tablename) in (select databasename, tablename from dbc.tables where tablekind = 'V')
;

.export reset;

create volatile table view_column_data_type
(
databasename varchar(30),
tablename varchar(30),
columnname varchar(30),
columntype varchar(30)
) primary index (databasename, tablename)
on commit preserve rows;

.run file /yourpath/view_col_type_sql.txt;

select *
from view_column_data_type
order by 1,2,3
;

.logoff;

N/A

Re: Datatype of a Column of a View

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.

Re: Datatype of a Column of a View

I have the following CASE statement in my view:

SELECT

  CAST((CASE EV.EVNT_CD

    WHEN 'A001' THEN 'SOMETHINGxxxxxxxx'

     ELSE 'UNKNOWN'

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?

N/A

Re: Datatype of a Column of a View

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)