Drop all prefixed tables at once

Search the sql server tables with some criteria and drop them all at one with script. No need to manually select and delete them one by one. This can save your lots of time if you have lots to tables to delete.

There are some needs when you need to drop lots of table from the sql server database. Manually deleting them can be very tedious and time consuming. You can write some automated scripts to filter the tables and drop them on  a single run. 

Here is the simple way to delete all the object from the sql server database given the prefix of the objects. Suppose you have created the tables with prefix "joo_", the below script can delete all objects with the prefix "joo_".




		USE mydatabase /*** Change to your Database name ***/

GO

declare names_curr cursor read_only forward_only for

SELECT r='Drop Table ' + SCHEMA_NAME(schema_id)+'.'+name

FROM sys.tables where name like 'tableprefix_%'

declare @tmp_field as varchar(255)

open names_curr

fetch next from names_curr into @tmp_field

while (@@FETCH_STATUS = 0)

begin

Exec(@tmp_field)

fetch next from names_curr into @tmp_field

end

close names_curr

deallocate names_curr
blog comments powered by Disqus