I read lots of examples on the internet that check if an object exists, and then drop it before creating it. You’d be excused for thinking that this is good practise. It’s not.
Dropping an object has negative side effects:
- Fisher emphasizes that all of the GRANTs and DENYs are lost.
- Extended Properties are also lost when an object is dropped.
- SYS.OBJECTS has both a CREATE_DATE and a MODIFY_DATE. Maybe I’m just an old DBA, but I like to be able to see those date differ.
The correct thing to do is to use Visual Studio database project, or some other database release manager, like liquibase.
Unfortunately those tools don’t work well for short examples on the internet, and a variety of other examples we invent to take half measures. If you want to take a half measure the correct half measure is
CREATE IF NOT EXISTS followed by an
DROP IF EXISTS followed by a
I use the following when I need to distribute code without Visual Studio:
IF NOT EXISTS (
WHERE NAME = 'prc_SaveLog'
AND type = 'P'
EXEC ('CREATE PROCEDURE dbo.prc_SaveLog AS THROW 50001, ''UnImplemented'', 1;');
ALTER PROCEDURE dbo.prc_SaveLog (@Parameter INT)
PRINT ‘Do Something’;