The SQL standard includes nothing for storing metadata.
You can either:
Comment your SQL source
With your own defined syntax that you'll parse, inside standard SQL comments:
CREATE TABLE dummy (col_name type ... /*@metadata_annotation*/, ...);
but you've got not guaranty that the RDBMS will retain them (in information_schema for example).
It is a case by case (RDBMS-by-RDBMS) solution, unless you control the DDL workflows.
Use the RDBMS-provided facility
There too, define your own naming.
Example in PostgreSQL:
CREATE TABLE dummy (col_name type ..., ...);
COMMENT ON COLUMN dummy.col_name IS '@metadata_annotation';
There you are even more tight to the RDBMS you want to run on.
Have your own meta-schema
CREATE TABLE my_annotations (s text, t text, c text, comment text);
INSERT INTO my_annotations VALUES ('my_schema', 'dummy', 'col_name', 'metadata_annotation');
This has the benefits of:
- being cross-RDBMS
you'll find some software vendors use it extensively to avoid being locked to a specific database… even when they are the RDBMS vendor too (e.g. Siebel).
On a related topic, database schema migration tools do store their metadata this way too
- well organized comments
instead of a fragile syntax that a single character can make unparseable
- being separated
you're not on competition for a single field, between the metadata writers and those who would like more human-friendly comments
- fine authorizations on who can write the schema
or even a stored procedure to validate
- automatization
all your checking (is each column tagged?) can be done in SQL
Dissociating input from storage
Note that you can combine this solution (for storing) and solution 1 (for annotating), if you control the DDL workflow and can intercept annotations in the SQL to transform them to INSERTs to your metaschema.
This would allow to validate the DDL SQL (any unannotated DDL would be rejected), but requires an extensive parser (not only on CREATE TABLE, but on ALTER TABLE too, be it to modify columns or to add new ones).
On the other hand, the declarative way in a custom schema-describing table necessarily decorrelates the annotations from the creation,
but you can run your validator (ensure that each column of information_schema.columns has a corresponding entry in your metaschema table) after each DDL file is played, and even on a regular basis (to ensure nothing bypassed your DDL workflow, creating columns either manually or via a stored procedure you couldn't catch).
Redundancy of information
Of course your metainformation will concentrate on functional annotations,
not technical ones that you can infer from the RDBMS-provided catalog,
like types and even constraints (information_schema.key_column_usage on PostgreSQL, MySQL, SQL Server and so on, all_cons_columns on Oracle).
If you really need to cross functional annotations to technical reality,
either have a view that aggregates both,
or, if you get them dedicated entries in your metaschema (for example you need to dump your DB with all its description in order to later restore it on another RDBMS where the description will serve to reconstruct all constraints in that new RDBMS' specific DDL),
make frequent consistency checks between the stored version and the database's catalog views.
