July 7, 2009 at 9:02 pm
Hi,
When I try to script multiple triggers or views from Management Studio, they get scripted using dynamic SQL:
EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].MyView ...
Is there a way to force it to script normal way, like:
CREATE VIEW [dbo].MyView ...
?
Thanks.
July 10, 2009 at 7:32 pm
You can accomplish this by highlighting the views you want to script in the Object Explorer Details tab, then right clicking on one of the veiws and selecting the option to script view as.
I tried it and it works.
July 11, 2009 at 4:33 am
It depends on your scripting options. If you select option "Include IF NOT EXISTS" you get this syntax because there is no other way to handle this for views, procs, functions, triggers, ... .
Another option would be Visual Studio for Database Professionals which compares against an existing database schema and only scripts objects which are not yet available or changed.
July 11, 2009 at 7:50 am
Florian Reischl (7/11/2009)
It depends on your scripting options. If you select option "Include IF NOT EXISTS" you get this syntax because there is no other way to handle this for views, procs, functions, triggers, ... .Another option would be Visual Studio for Database Professionals which compares against an existing database schema and only scripts objects which are not yet available or changed.
Flo, did you check out the way I suggested? All it did for me was script the three views I selected to a new window in SSMS which was the option I selected from the menu. I did not do it from the pop up menu in Object explorer.
July 11, 2009 at 8:50 am
Lynn Pettis (7/11/2009)
Flo, did you check out the way I suggested? All it did for me was script the three views I selected to a new window in SSMS which was the option I selected from the menu.
Did not try, sorry. Never tried a multi-select in "Object Explorer Details" view. Learned something new, thanks Lynn!
Roust_m
When I try to script multiple triggers or views from Management Studio, they get scripted using dynamic SQL:EXEC dbo.sp_executesql @statement = N'CREATE VIEW [dbo].MyView ...
Is there a way to force it to script normal way, like:
Just intended to answer the OPs question why SSMS creates the dynamic SQL statements and how to change this when using "Generate Scripts..." dialog.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply