Saturday, April 11, 2009

SQL scripts without sp_executesql

There is a totally useless scripting option in SSMS 2005 that you think might mimic the behavior of EM 2000 but it does quite the contrary.

Using Enterprise Manager in MSSQL 2000 you could generate a script to DROP and / or CREATE an object. The DROP statement used the "if exists" syntax, so you could replace an object with a new version. If you wanted to change an object (except for tables) you generate just the CREATE and then change that one word to ALTER. Simple stuff.

However, in SSMS there is an option "Include IF NOT EXISTS clause" which does several totally unnecessary things:

First of all, you will see

EXEC dbo.sp_executesql @statement = N'
all your statements are here
'
If you choose Create, it tests IF NOT EXISTS followed by CREATE, but you just scripted an object that exists, so it does nothing.

If you choose Modify, it tests IF NOT EXISTS followed by ALTER, but how can you alter an object that does not exist?

What a mess.

If you want to generate a script like 2000, then you need to turn that option OFF, and then separately generate the scripts for the DROP and the CREATE, and put them together.

No comments: