SQL Object Scripting "Check for object existance" doesn't work as desired in SSMS
Ever since SSMS 2012, using this option has produced undesirable code. It worked fine in SSMS 2008 and 2008R2 and I would like some way to restore that functionality.
When scripting out a view, I want the script to be:
IF (view exists) DROP VIEW;
CREATE VIEW blah blah blah;
I want two steps: Drop and Create. And I want the generated script to run correctly whether or not the view is already there. (this applies similarly to triggers, stored procedures, functions, indexes, etc... anywhere you can script for 'drop and create').
I loathe the current behavior of using either ALTER or Dynamic SQL in the 'create' step. I don't care if the script has to be divided into multiple batches, that's fine. Let me script things out the simple and most straight forward, least error-prone, and most maintable way please... simple IF EXISTS, DROP... followed by a simple CREATE.
We made some wrk in this area. Could folks out there take a look at the current “SSMS 18.0 Preview 6” and see if that meets your expectations?
If not, kindly enter a new UserVoice item.
Although I sympathize with your debacle, after all, if it were up to me we would all be writing code in Pascal (the most elegant and simple programming language I've known), the solution to your problem is to use the following structure:
IF OBJECT_ID('TableName') is null --Meaning it does not exist.
CREATE blah blah
else --Meaning it DOES exist.
<DROP blah blah>
I hope this helps. R.
That's sorta okay, but I need to support SQL Servers going back to before the CREATE OR ALTER.
What I want is what SSMS 2008 and SSMS 2008R2 did just fine. I have no idea why you changed it. It's unusable to me now. I have to keep SSMS 2008R2 around just to be able to script out things correctly.
I just want a "Script for Drop/Create" to do the right thing when the "test if exists" option is enabled: IF (exists) DROP, then CREATE, with no obnoxious dynamic SQL, or attempt to put the "if exists" on the create too, where IT IS REDUNDANT. Sure, do that if I'm only scripting for create, but if I'm specifically scripting for drop/create, then just do the right thing like SSMS 2008R2 did.
Olaf Rabbachin commented
I still keep SSMS 2008 (R2) because that seems to have been the last SSMS that scripted an object including an IF EXISTS ... DROP command rather than just the DROP.
In SSMS 2008: right-click i.e. a SP -> "Script Stored Procedure as ..." -> "DROP AND CREATE" + [whatever]
Can't be that hard - it's been there for ages!
Mark Freeman commented
I prefer to *not* do DROP then CREATE because of the loss of any custom permissions on the object. I want to CREATE if the object doesn't exist or ALTER if it does. Oracle's CREATE OR REPLACE is an excellent model. SQL Server should have CREATE OR ALTER syntax for all appropriate object types with no SSMS option needed.
Bascially, I should be able to leave the 'Test for Existence' option on at all times, and just have SSMS do the right thing. Right now, it doesn't. I have to constatnly go into settings and turn this option on and off so I can get the scripts to work correctly. I have to turn it on to script out the DROP, then go back in and turn it OFF in order to script out the CREATE. I just want scripts that are generated that are clear, free of unnecessary dynamic SQL, and that JUST WORK, regardless of whether the object already exists, or if I'm running this multiple times in a row. I have no idea why you broke this back in SQL 2012, or why it's six years later and you haven't fixed this yet. This is REQUIRED BASIC FUNCTIONALITY that is EXPECTED, and I have to keep SQL 2008 R2 around just to be able to script out stored procedures and views and such correctly... until this is FIXED.