views:

167

answers:

6

The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE. This is what I do instead.

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
 EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog')

CREATE PROCEDURE dbo.SynchronizeRemoteCatalog
AS BEGIN
    -- body
END

For triggers, you have to lean on the proprietary system views.

Is this the most accepted convention in the meantime?

EDIT: As n8wrl suggested, the official word suggests that this feature is not a high priority. Hence the question.

+4  A: 

Yes, that's what all of my build scripts look like. At the end I set the permissions as well.

David
I've always wanted this feature too, but for now, this is what mine look like as well.
rwmnau
Well, then the motion is passed. Seems the best we can do in the meantime (and I doubt I'll still be using SQL Server when and if it gets this feature), is to write good editor macros to make this easier.
harpo
+1  A: 

Looks like it's a while off: link text

typical script for me:

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'ig_InsertDealer' AND type = 'P')
    DROP PROC dbo.ig_InsertDealer
GO 
CREATE PROCEDURE dbo.ig_InsertDealer
...
GO
GRANT EXECUTE ON dbo.ig_InsertDealer TO ...
GO
n8wrl
I would recommend using a) the "sys" schema, and b) a more focused system view, e.g. sys.tables for tables, sys.triggers for triggers etc., instead of just the generic "sysobjects" (which will be deprecated soonish).
marc_s
A: 

That's basically the way to do it, yes. I just wonder if you have a particular reason to use the "EXEC" approach:

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
    EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog')

Why not just:

IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
    DROP PROCEDURE dbo.SynchronizeRemoteCatalog

???

For triggers, there's sys.triggers. Those are system catalog views in the "sys" schema - not strictly or directly tables, really.

Marc

marc_s
I started using EXEC because sometimes when I sent the script to clients where the objects did *not* exist, SQL Server would complain. Maybe it's superstition, but it hasn't hurt anything.
harpo
@harpo: thanks for the explanation
marc_s
+1  A: 

I'll use either depending on context: my initial-build or major refactoring scripts will use check/drop/create, pure maintenance scripts use alter.

overslacked
+1  A: 

I'd use OBJECT_ID(...) IS NOT NULL before a DROP.

Object identifiers have to be unique, so it works without using system tables:

CREATE TRIGGER dbo.ExistingTable ON dbo.AnotherTableFOR UPDATE
AS 
SET NOCOUNT ON
GO

gives

Msg 2714, Level 16, State 2, Procedure MetaClass, Line 3
There is already an object named ExistingTable ' in the database.

I normally use ALTER because of how we work with source control etc.

gbn
I finally get what you're saying here... and while OBJECT_ID is also proprietary, this is a much more compact way to perform the check.
harpo
I did not know OBJECT_ID is proprietary... it's probably in Sybase too though
gbn
+2  A: 

Every time a developer writes IF EXISTS(...) DROP a seal pup is clubbed. You should know exactly what's in the database and your upgrade script should do the CREATe or ALTER as appropiate, based on the current version of your application schema: Version Control and your Database.

Remus Rusanu
why? Better be safe than sorry! Have you never had a script fail on you and suddenly a sproc you were about to drop is still lingering around in the database? I'd always do a IF EXISTS() check first - just to be on the safe side! Have you never had a silly temp-DBA run some of your scripts several times instead of just once??
marc_s
I find this to be a reasonable approach to allowing my deployment scripts to be run many times safely. Besides, if I can club a seal without getting cold...
n8wrl
@marc_s: this is less of risk if you have strong controls and rely on ALTER (like we do). Any script I give to the DBAs is either safe to run multiple times or it's atomic so it errors out second time.
gbn
I guess I don't live in the ideal world. What do I say if a script raises an error on a client's machine, even after versioning and testing? "Too bad. It should work, so I can't help." I'd be out of business, Remus.
harpo
@marc: yes, silly dbas... I'm just trying to pland a seed here, get ppl to think at the db more as a resource under version control rather than the 'lets open ssms and modify out table!'. I know in practice is impossible not to rely on if exists() (or objec_id is not null, which I actually prefer, like gbn). I guess seal pups are doomed... arctic vile pest
Remus Rusanu
@gbn: maybe so - but if you have dba's in a different company and half the time they're really not pro's, it's hard to maintain "strict control". IF EXISTS() checks mitigate possible problems.
marc_s
@marc_s: the strict controls are on our side with source control, automatic synching, coldloads etc. So we can use ALTER. Over half of my DBAs are either Sybase or DB2 specialists, so I'm used this environment.
gbn
"Better be safe than sorry!" And in some environments, that means use alter when you can so you don't club the permissions on the object. In my past projects, developers set up initial permissions, but the DBAs were allowed, and did, modify permissions on the production DB.
Shannon Severance