Delete Tables with Certain Condition

emi_sastra
emi_sastra used Ask the Experts™
on
Hi All,

I want to delete (drop) tables with certain condition.

How could I do it?

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
declare @d int
set @d=5
if @d>0
begin
    drop table tableName
end
else
begin
    print 'can not drop'
end

Commented:
you can delete the data by using where clause..whar r u looking exactly??can you an example??


DELETE FROM customer
WHERE customer_name = 'IBM'

Open in new window

@shivkasi,

have you read Author's statement?

>>I want to delete (drop) tables with certain condition.<<

he want to drop table, not delete records. which could be done by the way I saw or this one:



if exists(select 1 from TableName where Field1='SomeCondition')
begin
   drop table tableName
end
else
begin
   print 'can not drop'
end

Open in new window

CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

try using bash/php, you can put conditions there

Author

Commented:
Hi All,

I am sorry, it seems that my description was not clear enough.

Suppose in my database I have tables below:

1. THSJPOJUAL1003
2. THSJPOJUAL1050
3. so on..

I want to delete tables where its name not less than THSJPOJUAL2000.

How could I do it?

Thank you.
Commented:
Try this code.
Tweak the condition until it outputs the right set of tables,
then un-comment the line:

--exec ('drop table ' + @tablename)

Please be very careful with this code and take backups of your database first!!!!
declare @tablename varchar (100)
select name into #tables from sysobjects where type = 'U'
declare tables_cursor CURSOR FOR
select name from #tables
open tables_cursor
fetch next from tables_cursor into @tablename
while @@fetch_status = 0
	begin
		if @tablename >= 'THSJPOJUAL2000'
		begin
			print @tablename + ' will be deleted'
			--exec ('drop table ' + @tablename)
		end
		fetch next from tables_cursor into @tablename
	end
close tables_cursor
deallocate tables_cursor

drop table #tables

Open in new window

Author

Commented:
Hi lammy82,

That's great.

My problem is solved.

Thank you very much for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial