SELECT object_name table_name, object_type as TABLE_TYPE, owner as table_owner FROM all_objects where object_type in ('TABLE','VIEW') and object_name = SELECT object_name table_name, object_type as TABLE_TYPE, owner as table_owner FROM all_objects where object_type in ('TABLE','VIEW') and object_name = SELECT object_name FROM all_objects where object_type in ( ) and ( object_name = or object_name = or upper(object_name) = ) order by case when object_name = then 1 when upper(object_name) = then 2 else 3 end SELECT col.COLUMN_NAME as name, CASE WHEN primaryConstraints.column_name IS NULL THEN 'false' ELSE 'true' END as primaryKey, /* Oracle has no equivalent to autoincrement or identity */ 'false' AS "IDENTITY", CASE WHEN col.NULLABLE = 'Y' THEN 'true' ELSE 'false' END as NULLABLE, col.DATA_TYPE as dbDataType, case /* 26 is the length of now() in ColdFusion (i.e. {ts '2006-06-26 13:10:14'})*/ when col.data_type = 'DATE' then 26 /* oracle returns 4000 for clobs which is the length of what Oracle stores inline in the record. However, oracle can store several gb out of line. */ when col.data_type in ('CLOB','BLOB') then 20000000 /* Oracle can compress a number in a smaller field so use precision if available */ else nvl(col.data_precision, col.data_length) end as length, col.data_scale as scale, col.DATA_DEFAULT as "DEFAULT", CASE WHEN updateCol.updatable = 'YES' THEN 'false' ELSE 'true' END as readonly FROM all_tab_columns col, all_updatable_columns updateCol, ( select colCon.column_name, colcon.table_name from all_cons_columns colCon, all_constraints tabCon where tabCon.table_name = AND colCon.CONSTRAINT_NAME = tabCon.CONSTRAINT_NAME AND colCon.TABLE_NAME = tabCon.TABLE_NAME AND 'P' = tabCon.CONSTRAINT_TYPE ) primaryConstraints where col.table_name = and col.COLUMN_NAME = primaryConstraints.COLUMN_NAME (+) AND col.TABLE_NAME = primaryConstraints.TABLE_NAME (+) and updateCol.table_name (+) = col.table_name and updateCol.COLUMN_NAME (+) = col.COLUMN_NAME order by col.column_id