• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

Dropping tables across multiple databases in sql server 2008

I need to drop tables which name that start from '_' across multiple databases. I wrote a cursor which only drop tables in DB1 only.
See cursor below:

declare @db_name nvarchar(200), @tbl_name nvarchar(200), @sql nvarchar(1000)

declare curDB cursor for
select name
from master.sys.sysdatabases
where name in('DB1', 'DB2', 'DB3', 'DB4')

open curDB
fetch next from curDB into @db_name

while @@FETCH_STATUS = 0

exec ('use '+@db_name)

declare curTbl cursor for
select table_name
from information_schema.tables
where table_type = 'BASE TABLE' and charindex('_',table_name) = 1

open curTbl
fetch next from curTbl into @tbl_name

while @@FETCH_STATUS = 0
      set @sql = ' drop table ' + '['+@tbl_name+']'
      print @sql
      exec ( @sql )

  fetch next from curTbl into @tbl_name
      close curTbl
      deallocate curTbl

fetch next from curDB into @db_name

close curDB
deallocate curDB

Any help appreciated!
  • 2
  • 2
3 Solutions
QlemoC++ DeveloperCommented:
You could try a combination of the (undocumented) system stored procedures sp_msforeachdb (to iterate thru all DBs) and sp_msforeachtable (to iterate thru all tables).
Going thru all tables beginning with an underscore:
exec sp_msforeachtable 'drop table ?', @replacechar= '?', @whereand = 'and o.name like ''\_%'' escape ''\'' '

Open in new window

and doing that for all DBs
exec sp_msforeachdb 'use ~; exec sp_msforeachtable ''drop table ?'', @replacechar= ''?'', @whereand = ''and o.name like ''''\_%'''' escape ''''\'''' '' ', @replacechar = '~'

Open in new window

If the usage of those many doubled single quotes is too much for you, you can use a var to set up parts of the string pre executing ;-).
Mark WillsTopic AdvisorCommented:
Or, get a list of tables, then drop them by building up dynamic SQL

use master

-- we want to gather the table names to be dropped, so we will use a temp table to store the names

if object_id('tempdb..#tbl_drops','u') is not null drop table #tbl_drops
create table #tbl_drops (id int identity,tbl_name varchar(500))

declare @sql1 varchar(max)
declare @sql2 varchar(max)

-- get all the table names that meet our requirements

select @sql1 = isnull(@sql1,'') + '
select table_catalog+''.''+table_schema+''.''+table_name from '+name+'.INFORMATION_SCHEMA.TABLES where left(table_name,1) = ''_''' from sys.sysdatabases

print @sql1

-- now store the found table names so we can then use dynamic SQL to drop them

insert #tbl_drops (tbl_name)
exec (@sql1)

-- now build up our second part of dynamic SQL to actually drop the tables found.

if scope_identity() > 0
   select @sql2 = isnull(@sql2,'') + 'drop table '+tbl_name +';
' from #tbl_drops

   print @sql2

-- comment out the exec until you triple check the print message 
   exec (@sql2)

-- we could use similar to above to first take a security copy just to be safe

-- now clean up
if object_id('tempdb..#tbl_drops','u') is not null drop table #tbl_drops

Open in new window

Now, instead of using a temp table, you could make it a permanent "audit" as a record of what has happened. In which case add in a date time and just check for that date time for building the dynamic drop part. Let me know if you want to explore...

Need to point out that this is potentially very dangerous and would be easy to do significant damage

tabushAuthor Commented:
Well, this will work only for the current database where the script runs. I need to do it for the databases I specify.
As a a matter of fact I already put together a script that will do this.
Appreciated your point about possible damage though.
QlemoC++ DeveloperCommented:
Sadly, sp_msForEachDB does not provide a whereand parameter, and so you need to check for the appropriate DB in e.g. an IF clause in the T-SQL command, like
'use ~; IF ''~'' IN (''DB1'', ''DB2'', ''DB3'') exec sp_msforeachtable ''drop table ?'', @replacechar= ''?'', @whereand = ''and o.name like ''''\_%'''' escape ''''\'''' '' ', @replacechar = '~'

Open in new window

Mark WillsTopic AdvisorCommented:
>> Well, this will work only for the current database where the script runs.

Nope, works across multiple DB's within the same instance.

You will note the USE MASTER to begin with.

To test, I created EE_TEST_DB, EE_TEST_DB1 and EE_TEST_DB2

Then I created "dummy" tables in each DB with a leading underscore.

It certainly removed each one of those tables....

It does assume you have correct permissions for each DB. And, should probably check for DB ID > 4 when building the list of DB's to check.

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now