SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Drop Any Object if it exists


Drop Any Object if it exists

Author
Message
rob 25361
rob 25361
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 6
Comments posted to this topic are about the item Drop Any Object if it exists
jhgoodwin
jhgoodwin
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 82
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
Oddvar Eikli
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 443
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
geoffrey.kimber2
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 175
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
rob 25361
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 6
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
rob 25361
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 6
Wrote this some time ago - > 5 years ago.
Happy for you to update, though it does the main important ones.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search