How does one extract the definition of a view using standard SQL?

Question

In trying to answer this question for myself I came across this nugget, after eventually adding "oracle" to my query terms:

select DBMS_METADATA.GET_DDL('TABLE','<table_name>') from DUAL;

Which works, but is not portable. How do I do the same thing on MySQL? SQLite? Others?

Answer 1

Since database metadata isn't standardized, there's no standard way to do this.

Answer 2

Well, there IS an ANSI standard called the INFORMATION_SCHEMA. Many vendors including Microsoft (SQL Server), Oracle, MySQL, Postgres support it, so that might be a first step.

For more information see this article here.

As for views, there's three INFORMATION_SCHEMA views for those:

• INFORMATION_SCHEMA.VIEWS
• INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
• INFORMATION_SCHEMA.VIEW_TABLE_USAGE

There is a column called "VIEW_DEFINITION" in the "INFORMATION_SCHEMA.VIEWS" view, so that would probably give you the information you need in a somewhat stadandized way.

Marc