Delete tables owned by.. 1

[ribsa3]

Hi there.

Could anyone send a tip my way on how to easiest delete all the tables in a database belonging to a certain user?

Much appreciated!

Kindly,
Bjoern Sandvik



--------------------------------------------------------------------
Do what you wish, as long as it harms no one. That includes yourself.

 

[NoCoolHandle]

If it was a user in the database, you could do something like...

use crap
go
declare @t varchar(300),@u varchar(300)
select @t='',@u='dbo'


while not @t is null
	Begin
		select @t=min(name) from sysobjects where 
		type = 'u' 
		and uid=(select uid from sysusers where name=@u) 
		and name>@t

		exec ('drop table ' + @u + '.' + @t)
		print @t + ' Dropped'
	end

Of course you will need to drop all constraints first, but that is just another loop..

HTH

Rob

 

[NoCoolHandle]

Oh yeah..

[bold]WARNING[/bold]

You need to make sure that you edit the above script to change
select @t='',@u=[red]'dbo'[/red]
to the user name (in the database) that you want to drop tables for.. If you don't it could (will) drop all tables owned by DBO.. As long as they dont have constraints protecting them.

Rob