David said:
Hi;
Is there a way to get the metadata for the tables and columns in a
database? What I really want is the description of each table and
column (yes I know that is optional information).
I need to be able to get it from OleDB and Oracle as well as
SqlServer.
OleDB has a method for that (see other posts) though it's very buggy:
it doesn't always give back results which are correct.
I pressume you want the 'Extended properties' for sqlserver and the
'Description' elements on oracle?
SqlServer:
SELECT sysobjects.Name AS ObjectName,
sysobjects.xtype AS ObjectType,
user_name(sysobjects.uid) AS SchemaOwner,
sysproperties.name AS PropertyName,
sysproperties.value AS PropertyValue,
syscolumns.name AS ColumnName,
syscolumns.colid AS Ordinal
FROM sysobjects INNER JOIN sysproperties
ON sysobjects.id = sysproperties.id
LEFT JOIN syscolumns
ON sysproperties.smallid = syscolumns.colid
AND sysproperties.id = syscolumns.id
ORDER BY SchemaOwner, ObjectName, ObjectType, Ordinal
The column 'ObjectType' can have a couple of different values: 'U'
(table), 'V' (view), and 'P' (procedure). If 'ColumnName' is filled,
it's an extended property of a field, if not, it's of the element
(table, view, proc).
Oracle:
Table/views:
SELECT * FROM ALL_TAB_COMMENTS WHERE OWNER = :schemaOwner ORDER BY
TABLE_TYPE ASC
// Table/view columns.
SELECT ALL_COL_COMMENTS.*,
ALL_TAB_COMMENTS.TABLE_TYPE
FROM ALL_COL_COMMENTS, ALL_TAB_COMMENTS
WHERE ALL_COL_COMMENTS.OWNER = :schemaOwner AND
ALL_COL_COMMENTS.TABLE_NAME=ALL_TAB_COMMENTS.TABLE_NAME
AND ALL_COL_COMMENTS.OWNER = ALL_TAB_COMMENTS.OWNER
ORDER BY ALL_COL_COMMENTS.TABLE_NAME ASC
This uses non-ansi joins so it also runs on Oracle 8i.
Frans
--
