Get View Ddl Using Query

Get VIEW ddl using query

Try the below query for view:

select text from ALL_VIEWS where upper(view_name) like upper(<view_name>);

For mviews:

select query from ALL_MVIEWS where upper(mview_name) like upper(<mview_name>);

how to get DDL (create query of a table) of a table from SQL Developer

DBMS_METADATA is the answer in general for Oracle. But, if you're using SQL Developer, like you tagged in your question, then you can simply use the DDL command.

What is DDL? It's a client command you can run in SQLcl and SQL Developer that constructs the dbms_metadata.get_ddl() code for you.

So let's:

• set our DDL options, how do we want it to look
• not include the commands in the spooled file
• not include the '4 rows selected' in our spooled file
• specify where we want the spooled file
• start the spool, set the filename
• ask for the ddl
• ask for the table rows in CSV
• turn the spool off

The Code:

clear screen
set ddl storage, segment_attributes off
set echo off
set feedback off
cd c:\users\jdsmith\desktop
spool regions.sql
ddl regions
select /*csv*/ * from regions;
spool off

How to retrieve the SQL used to create a view in Oracle?

I think the original text is lost:

create table t1(id number)
/
create view t1_vw as select * from t1
/
alter table t1 add val varchar2(20)
/
alter view t1_vw compile
/
select * from t1_vw
/

will return only id column.
Interesting, but for materialized views original text is preserved.

Is there a way to retrieve the view definition from a SQL Server using plain ADO?

Which version of SQL Server?

For SQL Server 2005 and later, you can obtain the SQL script used to create the view like this:

select definition
from sys.objects     o
join sys.sql_modules m on m.object_id = o.object_id
where o.object_id = object_id( 'dbo.MyView')
  and o.type      = 'V'

This returns a single row containing the script used to create/alter the view.

Other columns in the table tell about about options in place at the time the view was compiled.

Caveats

• If the view was last modified with ALTER VIEW, then the script will be an ALTER VIEW statement rather than a CREATE VIEW statement.

• The script reflects the name as it was created. The only time it gets updated is if you execute ALTER VIEW, or drop and recreate the view with CREATE VIEW. If the view has been renamed (e.g., via sp_rename) or ownership has been transferred to a different schema, the script you get back will reflect the original CREATE/ALTER VIEW statement: it will not reflect the objects current name.

• Some tools truncate the output. For example, the MS-SQL command line tool sqlcmd.exe truncates the data at 255 chars. You can pass the parameter -y N to get the result with N chars.

SSMS - get DDL of all views in a schema

If you want to get the view definitions from a query, you can use sql_modules.

select   m.definition
from     sys.sql_modules   m
join     sys.objects       o  on o.object_id = m.object_id
join     sys.schemas       s  on s.schema_id = o.schema_id
where    o.type = 'V'
and      s.name in ('your', 'schemas', 'here')

Customize as desired to select the views you want.

Generate create table DDL from View

In your local db, create a link to the 'read only' db:

CREATE DATABASE LINK READONLY_DB 
CONNECT TO scott IDENTIFIED BY tiger 
USING 'readonlydb';

Note, the USING 'readonbly' is referencing a tnsnames.ora entry for your readonly database. Substitute 'readonlydb' with the appropriate, correct value.

Then, with the db link created:

create table my_table as select * from readonly_table@readonly_db;

Where:

• 'readonly_table' represents the name of the table at the readonly database
• 'readonly_db' is the name of the database link you created in the first step.

---

Related Topics