Use "ALTER VIEW..." when exporting a view's DDL to the SQL Commander
a
anonymous
started a topic
about 9 years ago
[This topic is migrated from our old forums. The original author name has been removed]
Currently, when you export a view's DDL to the SQL Commander then it generates "CREATE VIEW..." however I use this on a daily basis to alter views. So I manually change "CREATE VIEW..." to "ALTER VIEW...".
It would be a great time saver if I would be able to change this default "CREATE" syntax into "ALTER" / "CREATE OR REPLACE" syntax via the Preferences.
Re: Use "ALTER VIEW..." when exporting a view's DDL to the SQL Commander
Hi Arjan,
Before I open a ticket on this, please explain why DROP and CREATE does not work for this scenario? Also, please tell us which database you are working with, since not all of them support ALTER and/or CREATE OR REPLACE.
Best Regards,
Hans
a
anonymous
said
almost 9 years ago
[This reply is migrated from our old forums. The original author name has been removed] [Attachment has been removed.]
Re: Use "ALTER VIEW..." when exporting a view's DDL to the SQL Commander
Hello Hans,
I am talking about the "Load SQL..." button from within the DDL tab of a view.
This copies the DDL into an SQL Commander.
When working with MS-SQL then I always change the following :
1. Change CREATE by ALTER
2. Add the schema name
3. Drop the column names
*1. This is the DDL that was exported by clicking on the button:*
CREATE VIEW
afdelingen
(
Afdeling,
Afdelingscode,
Afdelingsnaam,
Hierarchie,
str_code2,
Kostenplaats,
Afdelingsverantwoordelijke,
Afdelingsverantwoordelijke_uid
) AS
SELECT ...
*2. This is the modified DDL: *
*ALTER* VIEW
*dbo*.afdelingen
AS
SELECT ...
In Oracle the syntax would be
*CREATE OR REPLACE* VIEW
*dbo*.afdelingen
AS
SELECT ...
Hans Bergsten
said
almost 9 years ago
[This reply is migrated from our old forums.]
Re: Use "ALTER VIEW..." when exporting a view's DDL to the SQL Commander
Hi Arjan,
Thanks, then I understand. The DDL tab shows how an object was created, so changing it to an ALTER statement there does not seem right to me.
But there is already an Export View action in the right-click menu for a view object (as well as in the Actions button-menu in the Object View tab for a view). There you can check off both DROP and CREATE and get the statements loaded in an SQL Commander tab.
If this is something you do very often, you can also create a script with the corresponding @export and @ddl commands:
@export on;
@export set Destination="SQL Commander"
Filename=""
Format="SQL"
SqlEditor="New Editor"
SqlEditorPosition="Last"
SqlQualifyObjectName="true"
SqlSeparator=";";
@ddl view="${schema}$.${view}$" drop="true" native="true";
@export off;
I used DbVisualizer variables for the schema and view names here just to show that it is a possibility. With variables in the script, you get prompted for their values when you execute it.
Best Regards,
Hans
a
anonymous
said
almost 9 years ago
[This reply is migrated from our old forums. The original author name has been removed]
Re: Use "ALTER VIEW..." when exporting a view's DDL to the SQL Commander
The "Export View..." is indeed a solution for my needs.
I activated some additional settings (DROP/CREATE & Qualify Name) and saved these are my Default Export Settings.
This way I only need to remove the "view field names" when I add or remove fields from the select statement.
Thank you !
Hans Bergsten
said
almost 9 years ago
[This reply is migrated from our old forums.]
Re: Use "ALTER VIEW..." when exporting a view's DDL to the SQL Commander
Hi Arjan,
Great, I'm glad I could help.
Best Regards,
Hans
anonymous