Drop Any Object if it exists

Question

Post reply

Drop Any Object if it exists

rob 25361

SSC Enthusiast

Points: 100
More actions
July 28, 2014 at 8:51 pm

#293874

Comments posted to this topic are about the item Drop Any Object if it exists

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jhgoodwin SSC-Addicted Points: 466 More actions · Answer 1

You might want to consider adding support for:

1) sysname/nvarchar input

2) fully qualified names

Some good items that don't seem supported for testing:

1) item in another database

2) something with max synonym name length.

3) temp table with space in the name, or fully qualified temp table names

4) temporary stored procedure, or function

5) indexes

6) sql injection (your input is used right now as part of raw sql) eg: #a;

7) are you expecting to use this for columns?

8) drop command fallthrough - seems to run drop even if it does not exist. eg input: [some item that won't exist]

Just a few thoughts,

John

Oddvar Eikli SSC Journeyman Points: 91 More actions · Answer 2

The list of object types catered for is somewhat limited - object type have the following additional values:

AF = Aggregate function (CLR)

C = CHECK constraint

D = Default or DEFAULT constraint

F = FOREIGN KEY constraint

FS = Assembly (CLR) scalar-function

FT = Assembly (CLR) table-valued functionIF = In-lined table-function

IT - Internal table

L = Log

PC = Assembly (CLR) stored-procedure

R = Rule

RF = Replication filter stored procedure

S = System table

SN = Synonym

SQ = Service queue

TA = Assembly (CLR) DML trigger

TF = Table function

TT = Table type

X = Extended stored procedure

(http://msdn.microsoft.com/en-us/library/ms177596.aspx)

geoffrey.kimber2 Valued Member Points: 50 More actions · Answer 3

Ok, I'm just an advanced user, not a dba and I don't have a need for many functions, but I really like this one.

I've created the function on a database where I have appropriate permissions, but I've noticed a behavior I did not expecct.

When trying to delete more than one temp table, I have to separate the uses of the function by 'go'. It's not enough to use a semi-colon.

Do you know why this is? Is there a way to make this work wihtout inserting 'go' between uses?

Failing that, can the function be modified to allow dropping multiple tables at once?

Thanks for submitting this.

rob 25361 SSC Enthusiast Points: 100 More actions · Answer 4

Yeah that's the only pain with it because it doing a DDL it has to be the only statement in the execute set

Other than that I use it before every object create for scripts etc.

Hope that helps

rob 25361 SSC Enthusiast Points: 100 More actions · Answer 5

Wrote this some time ago - > 5 years ago.

Happy for you to update, though it does the main important ones.