I have kept "Check for object existence" option turned ON in my SQL Server Mgmt Studio, because I need SQL Server to automatically include "IF EXISTS" statements before dropping and recreating the objects.
Now, whenever I right-click and try to generate "Create and Drop scripts" for any objects, it puts the whole CREATE statement as a dynamic SQL which I think is a pure mess and more prone to errors.
Is there any option to make SSMS generating static SQL scripts when the object existence check is turned on?
I am using SSMS 2012.
If I understand you correctly, this is the correct / best approach...
The problem is, that there is no DROP_AND_CREATE statement in T-SQL. Many people use something like
IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME='SomeFunction')
DROP FUNCTION dbo.SomeFunction;
GO
CREATE FUNCTION dbo.SomeFunction...
But this is problematic, if there are dependencies like a CONSTRAINT or a GRANT or other...
It is easier to use a check like above and decide if you create the CREATE statement or if you change the first word to ALTER dynamically. You cannot place both statements in some kind of conditional workflow, as it has to be the first in batch.
... pure mess and more prone to errors
Especially with a generated statement I would not expect errors... You are right, that this isn't pretty, but many code generators do not produce pretty code. That doesn't really matter...