Take the 2-minute tour ×
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.

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

closed as off-topic by Paul White, tblPhil, bluefeet, Aaron Bertrand Jan 26 at 14:44

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Shopping list question - questions about which tool, library, product or resource you should use are off-topic here because they quickly become obsolete and often are just about the preferences of the answerer. If you have an issue with or a question about a specific tool, please revise your question to conform to that scope." – Paul White, tblPhil, bluefeet, Aaron Bertrand
If this question can be reworded to fit the rules in the help center, please edit the question.

5  
INFORMATION_SCHEMA is in the SQL standard contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt –  tblPhil Feb 6 '13 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 '13 at 20:27

3 Answers 3

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 '13 at 20:31

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 '13 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 '13 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 '13 at 17:57

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 '13 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 '13 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 '13 at 12:44

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