How to delet a "Rogue" table

Question

How to delet a "Rogue" table

jmin

SSC Veteran

Points: 208
More actions
October 1, 2009 at 7:59 am

#214937

The Setup: Database contains a table dbo.table1.
Some crappy code is executed:
select *
into table1
from table2
The database now contains a dbo.table1 and a table1, with a null owner. The issue now becomes, how to get rid of the "Rogue" table1 without affecting dbo.table1.
Is there a method other than dropping both and recreating the the proper dbo.table1?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

How are you determining that this second table1 is created with a Null owner?

It seems much more likely to me that the first table1 actually has the name [dbo].[dbo.table1], which would not conflict with the newly created table's name of [dbo].[table1].

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

jmin SSC Veteran Points: 208 More actions · Answer 2

The uid of the table in sysobjects does not exist in sysusers

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Hmm, what is the UID value that it lists?

Also, try this query and see if anything comes back:

SELECT * FROM sysobjects WHERE [name] LIKE 'dbo%'

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

jmin SSC Veteran Points: 208 More actions · Answer 4

SELECT * FROM sysobjects WHERE [name] LIKE 'dbo%' returns no results

SELECT substring(name,1,8)as name,id,xtype,uid FROM sysobjects WHERE [name] LIKE 'table%'

returns:

name id xtype uid

-------- ----------- ----- ------

table1 791673868 U 1

table1 887674210 U 9

table2 807673925 U 1

(3 row(s) affected)

select uid , status, substring(name,1,8)as name from SYSUSERS where UID in ('1','9')

Returns:

uid status name

------ ------ --------

1 2 dbo

(1 row(s) affected)

Lowell SSC Guru Points: 323518 More actions · Answer 5

interesting, what does this return:

SELECT substring(name,1,8)as name,id,xtype,uid,user_name(uid),suser_name(uid) FROM sysobjects WHERE [name] LIKE 'table%'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jmin SSC Veteran Points: 208 More actions · Answer 6

Check that.....

name id xtype uid user_name suser_name

-------- ----------- ----- ------ --------- ----------

table1 791673868 U 1 dbo NULL

table1 903674267 U 9 NULL NULL

table2 807673925 U 1 dbo NULL

(3 row(s) affected)