Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dropping tables across multiple databases in sql server 2008

Posted on 2013-12-25
5
Medium Priority
?
372 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 71

Assisted Solution

by:Qlemo
Qlemo earned 332 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 668 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 71

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

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.

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

609 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