Database metadata

This is an old revision of Database metadata, edited by BigChimp at . The current revision is 85682.

consolidate metadata info

Database metadata is data about a database. In this article, we also cover information about the database connection/server/client.

SQLDB

Server & client information

In FPC development versions as of November 2012, you can use GetConnectionInfo with the following parameters:

Not all SQLDB T*Connections support this; if there is no support the functions will return empty values

Firebird/Interbase specifics

In addition to the functions above, as of December 2012, the Firebird connector provides the GetODS function that shows the ODS major version of the database you are connected to. This ODS version determines what features are available for use - as you can e.g. use a database created with Firebird 2.1 with a Firebird 2.5 server, but the features will be limited to 2.1 functionality.

Schema information

Also known as a database's DDL (Data Definition Language), a schema is the representation of the tables, views, procedures and other objects that make up a database.

You can use the GetSchemaInfoSQL function to get an SQL string that you can run in a query to get information about metadata. Use the SchemaType parameters to request information about certain objects:

GetSchemaInfoSQL is not implemented equally for every connector; the Firebird connector currently has most of the functionality implemented; the others (e.g. MS SQL, Sybase, Oracle) have less functionality. Patches are welcome.

GetSchemaInfoSQL is used in Lazarus: in design mode, if you use the Object Inspector to edit the SQL property of a TSQLQuery where the database connection is connected, you can not only edit the text of the SQL query in the SQL Code tab, but also retrieve information in the Metadata tab by filling in some details and pressing on the Play/Go key:

Separate schema information

There is a separate FPC package that has support for schema information. This package is used in the LazDataDesktop project supplied with Lazarus.

Database specific information

Many databases have either

that show information about database objects as well as server version etc.

Examples are the @@VERSION variables in MS SQL Server and Sybase servers.

If your database connection library does not support other means of getting this information, you can use this (and submit a patch/improvement request on the relevant bug tracker for incorporation into SQLDB/Zeos etc)

See also