Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

Do you know of an industry standard that is designed to describe the metadata contents of a relational database (or part of it, such as tables, columns, etc.)?

Do you know if a tool exists that does export the metadata of any of the top 5 RDBMS tools in any format such as XML (without having to type in DDL in the syntax of particular database)?

Thank you.

share|improve this question
4  
INFORMATION_SCHEMA is in the SQL standard contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt – Phil Feb 6 at 14:15
@Phil, thanks for your input. If you care, you can make your comment an answer and I will up-vote it. – Emmad Kareem Feb 6 at 20:27

3 Answers

up vote 1 down vote accepted

There are several tools out there that can dump a database schema to XML.

You should be aware that "universal" tool might not catch all DBMS specific options (such as tablespaces, storage engines, special index types and so on).

The tools that come to mind are:

All three are Java/JDBC based and thus work with a wide range of DBMS and operating systems.

share|improve this answer
Thank you for your feedback. This is very close to what I wanted. – Emmad Kareem Feb 6 at 20:31
up vote 1 down vote

If you want a set of views that are "common" across database vendors (Oracle, MySQL, PostgreSQL, ...), there are the INFORMATION_SCHEMA views.

They are supposed to be a common standard across vendors (exactly how true I don't know). In SQL Server (versions >= 2000) you access them via [INFORMATION_SCHEMA].{view_name}, where {view_name} is any one of: 

CHECK_CONSTRAINTS,
COLUMN_DOMAIN_USAGE,
COLUMN_PRIVILEGES,
COLUMNS,
CONSTRAINT_COLUMN_USAGE,
CONSTRAINT_TABLE_USAGE,
DOMAIN_CONSTRAINTS,
DOMAINS,
KEY_COLUMN_USAGE,
PARAMETERS,
REFERENTIAL_CONSTRAINTS,
ROUTINE_COLUMNS,
ROUTINES,
SCHEMATA,
TABLE_CONSTRAINTS,
TABLE_PRIVILEGES,
TABLES,
VIEW_COLUMN_USAGE,
VIEW_TABLE_USAGE,
VIEWS.

Here's some tips on how to make use of them: http://www.mssqltips.com/sqlservertutorial/196/informationschematables/.

share|improve this answer
1  
Oracle does not support information schema views. – SQL Learner Feb 6 at 16:47
Thank you for your feedback. I was looking for something more generic. Each vendor may have different format for reporting the results. I was looking for a generic format that would work across different vendors. – Emmad Kareem Feb 6 at 20:30
Yes, Oracle is a bit of the oddball for standards compliance in that respect. The following 2 URLs might help: etutorials.org/SQL/SQL+Bible+Oracle/… (specifically Table 13-4) and alberton.info/oracle_meta_info.html#.URktN2eIp3A (try rolling your own). Also, the Wiki on RDBMS compliant with Information schema views: SQL Server, MySQL, PostgreSQL and SQLite. – Grignar Grenac Feb 11 at 17:57
up vote 1 down vote

Emmad, to answer your question there are commercial tools available that use a standards based approach to extracting data from ALL databases out there and storing them in a generic for like XML. The collection of such tools is know as Metadata Management Tools. The one I have extensively worked with is Informatica Metadata Manager version 8.6 and up.

It will connect to all the industry leading databases and extract all the common, informational metadata for you and even profile the data to give you counts and data types. The data is stored in a standard know as the Common Warehouse Model (CWM). You have an interface to browse the objects and annotate any additional definitions you may want.

Since you mentioned avoiding to type DDLs I believe you do not want a solution for hardcore DBAs. I hope this takes you towards finding the solution you seek.

share|improve this answer
Are you selling that product? – a_horse_with_no_name May 15 at 12:30
Nope, :) you have to go to the company Informatica or try IBM if your organisation has a standing relationship with them. Personally this is what worked well for me, failing having to build my own solution... So I apologise if it comes across that I am "selling" that product :) – Leslie at Acme Consultancy Ltd May 15 at 12:33
Just wanted to make sure. The answer does smell a little bit like it's spam. But if you are not related to the product it should be fine. – a_horse_with_no_name May 15 at 12:44

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.