CODEWITHSUNDEEP
sqlviewddl
Trevor Bramble
Trevor Bramble

Reputation: 8813

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

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?

Upvotes: 0

Views: 2943

Answers (3)

Darren
Darren

Reputation: 71

FOR SQL Server INFORMATION_SCHEMA.VIEWS limits the output length to 4000 characters, which to me makes it quite unreliable.

The following works much better with definition as nvarchar(max) and works on multiple object types (VIEW, SQL_STORED_PROCEDURE, SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_TABLE_VALUED_FUNCTION, possibly more, this is what I see in my DB)

SELECT TOP 1000
    O.name
    , O.type
    , O.type_desc
    , M.definition
FROM sys.Sql_modules AS M
    LEFT OUTER JOIN sys.objects AS O ON M.object_id=O.object_id

Upvotes: 3

marc_s
marc_s

Reputation: 754488

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

Upvotes: 3

S.Lott
S.Lott

Reputation: 391854

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

Upvotes: 1

Related Questions