Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do I remove a table where TABLE_SCHEMA IS NULL Expand / Collapse
Author
Message
Posted Monday, February 8, 2010 10:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 11, 2010 1:05 PM
Points: 3, 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.
Post #861840
Posted Wednesday, February 10, 2010 12:57 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, 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
Post #863574
Posted Thursday, February 11, 2010 11:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, February 11, 2010 1:05 PM
Points: 3, 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

Post #864203
Posted Thursday, February 11, 2010 12:12 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, January 3, 2014 10:59 AM
Points: 626, 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
Post #864245
Posted Thursday, May 16, 2013 12:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 21, 2014 11:31 PM
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.
Post #1453335
Posted Thursday, May 16, 2013 8:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 13,318, Visits: 12,803
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 Moden's 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)
Post #1453586
Posted Friday, May 17, 2013 4:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, November 21, 2014 11:31 PM
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.
Post #1453933
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse