Search code examples
sqlschemawinsql

SQL Schema changed to be "dbo.dbo." after altering table name

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,

Solution

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