object owner: URGENT please!

[SA]

There is a database with 200 tables for owned by userA and another 200 similar tables owned by another userB. How can I drop all the tables owned by userA in a transaction by querying on system table. I need the syntax giving the name of the system table etc.Any help is highly appreciated.
Thanks!

[Krishnan]

declare c1 cursor
for
select a.name from sysobjects a,sysusers b
where a.uid=b.uid and b.name='userA' and a.type='U'
declare @objectname varchar(128),@str varchar(1000)
open c1
fetch next from c1 into @objectname
while (@@fetch_status=0)
begin
select @str='drop table userA.' + @objectname
execute (@str)
fetch next from c1 into @objectname
end
close c1
deallocate c1

------------
SA at 2/7/01 3:11:48 PM

There is a database with 200 tables for owned by userA and another 200 similar tables owned by another userB. How can I drop all the tables owned by userA in a transaction by querying on system table. I need the syntax giving the name of the system table etc.Any help is highly appreciated.
Thanks!

[MAK]

Run this query . it creates 200 "drop table statements". Execut the output statements


select "drop table 'Usera."+a.name + "'" from sysobjects a,sysusers b
where a.uid=b.uid and b.name='Usera' and a.type='U'


-MAK

------------
Krishnan at 2/7/01 3:37:01 PM


declare c1 cursor
for
select a.name from sysobjects a,sysusers b
where a.uid=b.uid and b.name='userA' and a.type='U'
declare @objectname varchar(128),@str varchar(1000)
open c1
fetch next from c1 into @objectname
while (@@fetch_status=0)
begin
select @str='drop table userA.' + @objectname
execute (@str)
fetch next from c1 into @objectname
end
close c1
deallocate c1

------------
SA at 2/7/01 3:11:48 PM

There is a database with 200 tables for owned by userA and another 200 similar tables owned by another userB. How can I drop all the tables owned by userA in a transaction by querying on system table. I need the syntax giving the name of the system table etc.Any help is highly appreciated.
Thanks!

[SA]

THANKS FOR ALL YOUR HELP!!

------------
MAK at 2/7/01 4:03:40 PM

Run this query . it creates 200 "drop table statements". Execut the output statements


select "drop table 'Usera."+a.name + "'" from sysobjects a,sysusers b
where a.uid=b.uid and b.name='Usera' and a.type='U'


-MAK

------------
Krishnan at 2/7/01 3:37:01 PM


declare c1 cursor
for
select a.name from sysobjects a,sysusers b
where a.uid=b.uid and b.name='userA' and a.type='U'
declare @objectname varchar(128),@str varchar(1000)
open c1
fetch next from c1 into @objectname
while (@@fetch_status=0)
begin
select @str='drop table userA.' + @objectname
execute (@str)
fetch next from c1 into @objectname
end
close c1
deallocate c1

------------
SA at 2/7/01 3:11:48 PM

There is a database with 200 tables for owned by userA and another 200 similar tables owned by another userB. How can I drop all the tables owned by userA in a transaction by querying on system table. I need the syntax giving the name of the system table etc.Any help is highly appreciated.
Thanks!