Delete all tables with a certain prefix name in a SQL 2008 database programmatically

Question

I have 3000+ tables in my SQL 2008 database with names like listed below, that all starts with tempBinary_, that I need to delete programmatically, how do I do that? I don't know if I prefer the solution in a SQL-script or with use of LINQtoSQL, i guess both are fine.

tempBinary_002c90322f4e492795a0b8a14e2f7c99 tempBinary_0039f7db05a9456f96eb3cd6a788225a tempBinary_0057da9ef0d84017b3d0bbcbfb934fb2

I've used Like before on columns, but I don't know if it good for table names too. Maybe something like this, where LIKE is used, can do it? I don't know.

Use [dbo].[database_name]
DROP TABLE table_name
WHERE table_name LIKE 'tempBinary_%'

Any ideas?

I think the solution might be close to this answer. http://stackoverflow.com/questions/4424038/how-to-delete-all-tables-with-prefix-bkp-from-a-given-database — radbyx, Jul 04 '11 at 08:33

Adrian Iftode · Accepted Answer · 2011-07-04T09:19:18.567

2

declare @stmt varchar(max) = ''
declare @tbl_name varchar(255)


DECLARE tbl_cursor CURSOR  FORWARD_ONLY READ_ONLY
    FOR select name 
        from sysobjects 
        where xtype='u' and name like 'tempBinary%'
OPEN tbl_cursor
FETCH NEXT FROM tbl_cursor
INTO @tbl_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    set @stmt = @stmt + 'drop table ' + @tbl_name + ';' +  CHAR(13)


    FETCH NEXT FROM tbl_cursor 
    INTO @tbl_name
end
CLOSE tbl_cursor;
DEALLOCATE tbl_cursor;    

execute sp_sqlexec @stmt

edited Jul 04 '11 at 09:19

answered Jul 04 '11 at 08:41

Adrian Iftode

15,465
4
48
73

if you put a print @stmt before execute sp_sqlexec you'll see the drop statements line by line – Adrian Iftode Jul 04 '11 at 08:58
Allright thanks. Hold on I'll test it, and get back to you as soon as I can. – radbyx Jul 04 '11 at 09:10
I made a test, is kind slow this script, the only improvement is to declare the cursor FORWARD_ONLY READ_ONLY, I'll edit – Adrian Iftode Jul 04 '11 at 09:18

Delete all tables with a certain prefix name in a SQL 2008 database programmatically

1 Answers1