Metadata using SQL

If there a way -using SQL- to fetch the meta data for a database or table,
and return the results in a dataset form?

Result dataset could be something like this:

TableName FieldName FieldType FieldSize
TBLA ID AUTOINC
TBLA NAME VARCHAR 50
TBLB ID GUID
TBLB DESCR MEMO


TIA - Hans

Hans,

<< If there a way -using SQL- to fetch the meta data for a database or
table, and return the results in a dataset form? >>

You can check out the available metadata views by running:

select * from #meta

<< Result dataset could be something like this: >>

select * from #fields

Ole

Thanks Ole, I knew it wouldnt be hard...

Hans

Yes!

Select * from #SCHEMAS gives me a nice result set of aliases and ther
folders...

So why is the next statement giving me an error?

INSERT INTO #SCHEMAS VALUES ('TEST','C:\TEST')

NexusDB: Query586651328: Invalid handle to the function. [$2706/9990]

Or is this statement invalid in SQL anyway? I can imagine it being possible
to add, modify and delete an alias using SQL this way...

DELETE FROM #SCHEMAS WHERE SCHEMA_NAME='TEST'

By the way, returns the same invalid function error.

Hans,

<< Select * from #SCHEMAS gives me a nice result set of aliases and ther
folders...

So why is the next statement giving me an error?

INSERT INTO #SCHEMAS VALUES ('TEST','C:\TEST')

NexusDB: Query586651328: Invalid handle to the function. [$2706/9990] >>

The metadata views are read-only in-memory tables. You can only query these
views.

Ole

What a pity.. It would have been a great option to add aliases etcetera
using SQL....

Hans,

<< What a pity.. It would have been a great option to add aliases etcetera
using SQL.... >>

Well, I don't think any RDBMS would allow users to directly manipulate the
system tables (metadata). The system tables are updated by the database
server as an effect of users running DDL statements.

I believe NexusDB V3 might support CREATE CATALOG and CREATE SCHEMA
statements.

Ole

> I believe NexusDB V3 might support CREATE CATALOG and CREATE SCHEMA
> statements.

Very likely yes.

--

Hannes Danzl [NexusDB Developer]
Newsgroup archive at http://www.tamaracka.com/search.htm

> << What a pity.. It would have been a great option to add aliases etcetera
> using SQL.... >>
>
> Well, I don't think any RDBMS would allow users to directly manipulate the
> system tables (metadata).

InterBase and Firebird do, although Jim Starkey now says it was a bad idea
:-)

MS SQL Server does, if you turn it "on" so that it allows you to.


>The system tables are updated by the database
> server as an effect of users running DDL statements.

Having an extensive set of DDL statements is by far the best way. Having
them under transaction control is even better.


--
Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com