2

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.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Squall Lionheart
  • 43
  • 4
  • 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. http://weblogs.sqlteam.com/joew/archive/2007/10/23/60383.aspx – Nick.Mc Apr 30 '15 at 03:06

1 Answers1

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 ?'
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91