SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How do I remove a table where TABLE_SCHEMA IS NULL


How do I remove a table where TABLE_SCHEMA IS NULL

Author
Message
relentless99
relentless99
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 20
I have an application that creates and removes tables as part of its process. For some reason it doesn't always remove the tables it creates.
Running on SQL Server 2000 this does not create an issue, however I have upgraded to SQL 2005.
When I attempt to view the tables in Management Studio I receive an error "Value cannot be NULL" and no tables are displayed.

The following code displays the culprit:

SELECT TABLE_SCHEMA,TABLE_NAME, OBJECTPROPERTY(object_id(TABLE_NAME), N'IsUserTable') AS type FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA IS NULL

TABLE_SCHEMA TABLE_NAME type
--------------- ------------- ------
NULL GRSA0000002H5 NULL


How can I remove this table with a Table_Schema of NULL?
I've tired:

ALTER SCHEMA dbo TRANSFER NULL.GRSA0000002H5

UPDATE INFORMATION_SCHEMA.TABLES SET TABLE_SCHEMA = 'dbo' WHERE TABLE_SCHEMA IS NULL

as well as some code I found using 'sp_changeobjectowner'.

The response I get is either it doesnt exist or I dont have permissions.
SQLBOT
SQLBOT
SSC Eights!
SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)

Group: General Forum Members
Points: 948 Visits: 836
what an odd problem.

what's your DB's compatibility level ?

also what does this tell you


select * from sys.schemas

select schema_id, * from sys.objects

where name = 'GRSA0000002H5'



Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
relentless99
relentless99
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 20
Compatibility Level is SQL Server 2000

In the sys.schemas query there is no schema_id with the value of 122 as is displayed in the
results from the sys.objects query.

schema_id: 122
name: GRSA0000002H5
object_id: 419688743
principal_id: NULL
schema_id:122
parent_object_id: 0
type: U
type_desc: USER_TABLE
create_date: 2006-11-01 14:47:55.077
modify_date: 2006-11-01 14:47:55.077
is_ms_shipped: 0
is_published: 0
is_schema_published: 0
SQLBOT
SQLBOT
SSC Eights!
SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)SSC Eights! (948 reputation)

Group: General Forum Members
Points: 948 Visits: 836
freaky. I think the SQL 2000 compat level might have something to do with it.


The only thing I can think of is to allow system catalog updates and insert that row into sys.schemas
or change the row in sysobjects to use the dbo schema.


you definitely have a ghost in the database.

Craig Outcalt



Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258
dao.tran
dao.tran
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 58
Hi,

I'm having exactly the same problem, please let me know how to drop this table as I have tried all solutions above and none work.
Also allow system catalog updates is not allowed so I'm unable to update the system table.

Many thanks,
Dao.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25960 Visits: 17519
dao.tran (5/16/2013)
Hi,

I'm having exactly the same problem, please let me know how to drop this table as I have tried all solutions above and none work.
Also allow system catalog updates is not allowed so I'm unable to update the system table.

Many thanks,
Dao.


How exactly do you end up getting a table with no schema?

Are you able to select from the table? If so, maybe you can use alter schema?

http://msdn.microsoft.com/en-us/library/ms173423.aspx

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
dao.tran
dao.tran
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 58
I'm not sure how the developer/application managed to create this object with a schema_id that does not exist in sys.schemas. Maybe it has something to do with database compatibility 80 on SQL 2005.
I had it resolved by using DAC in single mode and updated this object in sysschobjs to dbo id and it works fine.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search