Solved

Dropping tables across multiple databases in sql server 2008

Posted on 2013-12-25
5
351 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
  • 2
  • 2
5 Comments
 
LVL 68

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 68

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now