Re: Proc to get column names/types for a view

Art Krumsee wrote:

> I have a stored procedure I've been trying to port from SQL Server to
> Oracle. It takes the name of any view or table and returns the names and
> types of all fields within that view. I've been able to accomplish the same
> in Oracle to display the fields in a table but not in Views.
>
> I understand that Oracle and SQL Server are very different animals and that
> Oracle doesn't use system tables in the same way that SQL Server does so
> this isn't some simple port. Still, this seems like such a valuable
> procedure that I'm hoping someone has built something similar in Oracle.
>
> For what its worth, this is the SQL Server statement:
>
> SELECT cast(syscolumns.name as varchar(25)) as field,cast(systypes.name as
> varchar(20)) as type,syscolumns.length
> FROM syscolumns,systypes
> WHERE id = (select id from sysobjects where name=''' + @ViewName + ''') and
> syscolumns.xtype = systypes.xtype
> ORDER by field'
>
> Art

You will find both as follows:

SELECT column_name, data_type, data_length FROM all_tab_columns
WHERE table_name = <table_or_view_name>;

Daniel Morgan Received on Thu Mar 06 2003 - 19:36:46 CST