Solved

Dropping tables across multiple databases in sql server 2008

Posted on 2013-12-25
5
361 Views
Last Modified: 2014-01-01
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
begin

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
  begin
      set @sql = ' drop table ' + '['+@tbl_name+']'
      print @sql
      exec ( @sql )

  fetch next from curTbl into @tbl_name
  end
      close curTbl
      deallocate curTbl

fetch next from curDB into @db_name
end

close curDB
deallocate curDB

Any help appreciated!
0
Comment
Question by:tabush
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 69

Assisted Solution

by:Qlemo
Qlemo earned 83 total points
ID: 39739888
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 ;-).
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 167 total points
ID: 39739955
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
begin
   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)
end

-- 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

SO PLEASE BE CAREFUL
0
 
LVL 2

Author Comment

by:tabush
ID: 39741845
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.
Thanks.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 39742100
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

0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 167 total points
ID: 39742173
>> 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.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question