It’s amazing the things that SQL can do these days. It’s also amazing how many of them I’ve managed to completely miss over time. I was having a random conversation with a friend on Twitter the other day and they mentioned an acronym DIE. After asking for an explanation (and I’m really glad I did) they gave me this:
DROP DATABASE IF EXISTS DBName1, DBName2, DBName3;
After picking my jaw up off the floor I did a bit of looking. It turns out the DROP xxxx IF EXISTS name,…n is now the pattern for dropping stuff. For example:
DROP PROCEDURE IF EXISTS SPName1, SPName2, SPName3;
DROP VIEW IF EXISTS ViewName1, ViewName2, ViewName3;
DROP TABLE IF EXISTS DBName1.SchemaName.TblName1, SchemaName.TblName2, TblName3;
This is HUGE. It makes code so much more readable and less likely to error. For me it’s right up there with the CREATE OR ALTER pattern. No more DROP then CREATE or ALTER, no more IF EXISTS () DROP xxxx.
Both of these have been around since SQL 2016 so I really don’t have any good excuses for not already knowing them but at least now I do! (And so do you in case you’d missed it like I did.)