SQL Server Enterprise Manager - bulk delete tables and change ownership of tables

Question

I have almost no experience with SQL Server Enterprise Manager, so I'm not sure if this is possible (or, hopefully, ridiculously simple!)

During import into the database, something happened where each table duplicated itself with two important differences.

Firstly, the owner on both tables is different, the second is that only the copy is copied by the structure on one of the copies.

Soda's law showed that, of course, the data was stored on tables belonging to the wrong person, so my question is: can I quickly delete all tables belonging to one user, and can I quickly change the ownership of all other tables to bring them in line.

There are enough tables that automation will become my preferred LONG option!

Any help would be greatly appreciated, I am running SQL Server 2000

Answer 1

declare @emptyOwner varchar(20) declare @wrongOwner varchar(20) declare @emptyOwnerID bigint declare @wrongOwnerID bigint declare @tableName nvarchar(255) set @emptyOwner = 'dbo' set @wrongOwner = 'guest' select @emptyOwnerID = (select uid from sysusers where name = @emptyOwner) select @wrongOwnerID = (select uid from sysusers where name = @wrongOwner) select name as tableName into #tempTable from systables where type='U' and exists (select 1 from systables where type = 'U' and uid = @emptyOwnerID) and exists (select 1 from systables where type = 'U' and uid = @wrongOwnerID) declare @dynSQL nvarchar(MAX) declare ownme cursor for  select tableName from #tempTable open ownme fetch next from ownme into @tableName while @@FETCH_STATUS = 0 begin   @dynSQL = 'DROP TABLE [' + @emptyOwner + '].[' + @tableName + ']'   exec(@dynSQL)   @dynSQL = 'sp_changeobjectowner ''[' + @wrongOwner + '].[' + @tableName + ']'',''' + @emptyOwner + ''''   exec(@dynSQL)   fetch next from ownme into @tableName end close ownme deallocate ownme 

Answer 2

To change ownership, see: Changes to SQL table properties, quick and easy

The code shown in the link above:

 DECLARE @old sysname, @new sysname, @sql varchar(1000) SELECT @old = 'oldOwner_CHANGE_THIS' , @new = 'dbo' , @sql = ' IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?'' AND TABLE_SCHEMA = ''' + @old + ''' ) EXECUTE sp_changeobjectowner ''?'', ''' + @new + '''' EXECUTE sp_MSforeachtable @sql