Proc to get column names/types for a view

From: Art Krumsee <akrumsee_at_columbus.rr.com>
Date: Fri, 07 Mar 2003 00:33:50 GMT
Message-ID: <ONR9a.2719$s75.1429231@twister.columbus.rr.com>

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 Received on Thu Mar 06 2003 - 18:33:50 CST