Search code examples
sql-serversql-server-2000

How do you change the table_schema when the current table_schema is null?

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

Solution

  • 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:

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