Better ALTER than DROP

In this article, I am going to provide a construction for deleting an object before creating it.

In our team, there are about twenty SQL Ninja developers. All of them describe this construction in different ways.

Our team consists of about twenty SQL Ninjas and all of them use the following statement in a different way:

Or:

Or:

On StackOverflow, users liked this version:

The stars are aligned and I found an appropriate implementation in one of SQL sites. At first, I was shocked, but then people helped to see why it works well.

The point is that if you use the DROP and CREATE statements each time, then you delete object permissions. In addition, the object can be in the replication and it will be deleted as well once re-created.

So, I liked this version and decided to wrap it up into the dbo.antidrop procedure.

The procedure takes only two arguments: object name and its type. To check the object type, execute the following statement:

Here is how it will look like:

Finally, the code of the procedure is as follows:

Thank you for your attention!

Lestat Kim

Lestat Kim

Lestat Kim is self-employed, MySQL and Python developer. In addition, he is a Bitcoin Trader.
Lestat Kim

Latest posts by Lestat Kim (see all)

Lestat Kim

Lestat Kim is self-employed, MySQL and Python developer. In addition, he is a Bitcoin Trader.