Solved

Dropping tables across multiple databases in sql server 2008

Posted on 2013-12-25
5
356 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 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Run SQL Server Proc from Access 11 31
convert null in sql server 12 34
MS SQL BCP Extra Lines Between Records 2 19
T-SQL Default value in Select? 5 27
In this article I will describe the Backup & Restore 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.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

809 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