Drop Tables

Question

Drop Tables

mark richardson

SSC Eights!

Points: 807
More actions
January 14, 2005 at 2:37 am

#162984

Hi all,
I have a third-party product that, as part of it's routines, creates 'ad-hoc' tables but unfortunately does not delete them after it runs.
Is there a command that allows me to drop these?
ie: drop tables where crdate in sysobjects = yesterday
Thanks in advance

Viewing 8 posts - 1 through 7 (of 7 total)

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

~Sreejith G SSCrazy Points: 2311 More actions · Answer 1

Its better not u use such type of 3rd party products, I cannot even beleive that its creating so much ad-hoc tables and not deleting it... then why do we have the temporary table concepts.... Are u sure that these tables are not needed... Might be they clear these tables after a specific period of time or so... Iam not sure.... And about what u r saying... like deleting table from sysobjects is not a good practice.... dont blame after a time u deleted ur own table... .....

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

A quick and dirty basic construct (untested) might be:

DECLARE @stmt NVARCHAR(200)

WHILE EXISTS

(

SELECT

*

FROM

sysobjects

WHERE crdate> < your criteria >

AND OBJECTPROPERTY(id,'IsUserTable')=1

&nbsp

BEGIN

SELECT

@stmt = 'DROP TABLE ' + name

FROM

sysobjects

WHERE crdate > < your criteria >

AND OBJECTPROPERTY(id,'IsUserTable')=1

EXEC sp_ExecuteSQL @stmt

END

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mark richardson SSC Eights! Points: 807 More actions · Answer 3

Thanks Frank.

Looks ok but only problem is, I haven't got the sp_execute sql Extended stored procedure in the DB i am working on so:

how can i use the sp_executesql in the master DB against my db or, how can i copy it to my db ?

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Just use EXEC() instead. The difference is well explained here: http://www.sommarskog.se/dynamic_sql.html

But as your are using this for adminsitrative purposes only, both are equally good.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Philip Kelley SSCertifiable Points: 6351 More actions · Answer 5

I have to mention, this solution would make it difficult to intentionally add new tables to your system...

When you run the third party app (say) the second time, does it drop and recreate the tables, or does it blow up? If you run it frequently, you may be better off just leaving them there. If it's an infrequent one-shot job, you could manually run Frank's script, or work that in a call to the app--save the time before you run, and after the run delete all tables created after the saved datetime.

PHilip

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Either that way or by adding other criteria to the WHERE clause. if the vendor has certain patterns in his naming convention. Anyway, I mentioned it's a basic construct and as such should be handle with care.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 7

how can i use the sp_executesql in the master DB against my db or, how can i copy it to my db ?

You should be able to use sp_executesql in any database. Master has a bunch of stored procs that can be used in any database.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor