Odtnhj

I somehow managed to create a table in a database with a null table schema.
I can't query the table since it has no owner, and altering the table doesn't work for the same reason.

I would alter the table using:

ALTER SCHEMA null TRANSFER dbo.SubscriptionAnswerMR

But that doesn't work.

The information_schema.tables looks like this:

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
q_Profiles NULL SubscriptionAnswerMR BASE TABLE

So my question is: How do I change q_Profiles' table_schema?

SQL Server 2000 (edit)
Microsoft SQL Server Management Studio 2008R2

You should be able to verify that your table is fine by seeing the result of the following query:

SELECT u.name
 FROM q_Profiles..sysobjects AS o
 INNER JOIN q_Profiles..sysusers AS u
 ON o.uid = u.uid
 WHERE o.name = 'SubscriptionAnswerMR';

This should be dbo unless someone explicitly created them with a different owner or used sp_changeobjectowner. Which you can use if you find that sysobjects also has the wrong answer:

EXEC sp_changeobjectowner 'SubscriptionAnswerMR', 'dbo';

ALTER SCHEMA is not valid here because it was introduced in SQL Server 2005. Though it would be useful for you to describe what "doesn't work" means.

INFORMATION_SCHEMA is a horribly unreliable set of views as @Pondlife points out. Also see the following, which doesn't help you much in SQL Server 2000, but should help going forward:

• The case against INFORMATION_SCHEMA views

Also as a side note you seem to be confused about tables and database. TABLE_CATALOG is the database, not the table.

Did you note this comment in the documentation?

Do not use INFORMATION_SCHEMA views to determine the schema of an
object. The only reliable way to find the schema of a object is to
query the sys.objects catalog view or use the OBJECT_SCHEMA_NAME
function.