How to delete selected 100's of temp database in easy way

Question

How to delete selected 100's of database in an easy way? I'm using SQL Server.

Example:

dbo.temp1
dbo.temp2
dbo.temp3
.
.
.
dbo.temp98
dbo.temp99
dbo.temp100
dbo.temp101
dbo.temp102

I just want to delete dbo.temp1 to dbo.temp100.

You could use the undocumented sp_MSForEachTable stored procedure to run a script for every table. This script would check the name and drop it. You can probably find an example of this somewhere. weblogs.sqlteam.com/joew/archive/2007/10/23/60383.aspx — Nick.Mc
– Nick.Mc, Commented Apr 30, 2015 at 3:06

Nick.Mc · Accepted Answer · 2015-04-30 03:12:09Z

2

First run this to check it lists only the tables you want to drop:

EXEC sp_MSForEachTable 'IF LEFT(''?'',11) = ''[dbo].[temp''  PRINT ''?'''

If it does, run this:

EXEC sp_MSForEachTable 'IF LEFT(''?'',11) = ''[dbo].[temp''  DROP TABLE ?'

answered Apr 30, 2015 at 3:12

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1