0

I tried to alter a table with a new table name. I successfully changed the name but the schema also changed from "dbo." to be "dbo.dbo.". When I tried selecting data from the new table, it shows in the Message box that the table is invalid so I can't do anything with the new table.

Does anyone know how can I change the table back to the original schema? I used WINSQL with sql server 2008. Thanks,

user2979630
  • 131
  • 1
  • 5
  • 2
    I don't think you can change the schema name using ALTER. Please post the ALTER statement that you executed –  Jan 03 '14 at 20:49
  • is "dbo.dbo" an actual schema on your DB? – T McKeown Jan 03 '14 at 20:49
  • @Nicarus at first i thought OP got confused with `ALTER SCHEMA ... TRANSFER` which is pretty easy to do, but after thinking about it it seems like OP just right-clicked and renamed. – swasheck Jan 03 '14 at 21:09
  • @swasheck I completely agree –  Jan 03 '14 at 21:10

1 Answers1

11

My guess is that you've actually simply renamed the table to [dbo.tablename] and its fully qualified name is [dbname].[dbo].[dbo.tablename]. This happens when you right-click to rename a table name in SSMS and I'd imagine that WinSQL is doing the same thing (though I don't know why you're using that tool when SSMS is included). When you right-click, it takes away the schema name which makes you believe you need to fully qualify the new name, but you don't.

You should be safe to right-click and rename the table name to just the new table name.

To be sure, though, you can run:

select * 
    from sys.schemas
 where name = 'dbo.dbo';

just to confirm that you've not created a new schema.

EDIT

Just for the sake of completeness I'll incorporate the comment made by @billinkc:

Run this query to get the exact schema of the table:

select 
    s.name as SchemaName, 
        t.name as TableName
from sys.schemas s
    join sys.tables t 
    on s.schema_id = t.schema_id
where t.name = 'tablename'
swasheck
  • 4,644
  • 2
  • 29
  • 56
  • 2
    +1 I think this is probably the safest assumption and the only one that makes sense to me based on the information in the question. –  Jan 03 '14 at 21:07
  • 2
    Alternatively, `SELECT S.name AS SchemaName, T.name As TableName FROM sys.schemas AS S INNER JOIN sys.tables AS T ON T.object_id = S.object_id WHERE T.name like '%MyTable'` will reveal the precise schema and name of the table – billinkc Jan 03 '14 at 21:43
  • @billinkc very true, but my suspicion does not reach that depth (given the nature of the question), thus i didnt dive that deeply :) – swasheck Jan 03 '14 at 21:44
  • swasheck: Thanks for your answer!! My schema didn't really change based on your query. Schema is still "dbo". What changed was the table name, which shows the table name as 'dbo.tablename' instead of 'tablename'. – user2979630 Jan 06 '14 at 16:15
  • BTW, there is no GUI in WINSQL to change(at least I can't find if) the table query-free. I used "EXEC sp_rename 'old_Table', 'new_Table_10'" to change the table, causing the problem. I have also found the solution to change the table name back without 'dbo' using the query "EXEC sp_rename '"dbo.new_Table"', 'old_Table_10'". Be aware that you have to add big quote for dbo.table. – user2979630 Jan 06 '14 at 16:22