CODEWITHSUNDEEP
sql-serversql-server-2000
Matt R
Matt R

Reputation: 2617

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

Upvotes: 0

Views: 973

Answers (2)

anon
anon

Reputation:

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.

Upvotes: 2

Pondlife
Pondlife

Reputation: 16240

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.

Upvotes: 1

Related Questions