How to remove or alter the dbo prefix from SQL Server 2012 tables?

Question

Someone already had asked this question here but didn't get an answer for that question .

How can I change a table in SQL Server 2012 that starts with a dbo prefix to one without ?

Solution 1

Here is the answer (alter !!!) , for anyone that might need it someday :

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'MyDbo')) 
BEGIN
    EXEC ('CREATE SCHEMA [MyDbo] AUTHORIZATION [dbo]')
END

ALTER SCHEMA MyDbo 
    TRANSFER dbo.your_old_table
GO

Solution 2

change > mydb.tableName to dbo.tableName

1 .Click F4 or ( view > Properties window) , 2 .then click on your Table. 3 .in properties window , in schema field , change the attribute.

Comments

[Shaikh Farooque]

Do you have any situation? Because it is useful from performance prospective.

[marc_s]

You cannot - at least not to without anything. A table is always in a schema - it can be called dbo (the default) or something else - but you cannot have "no schema" ....

[Luaan]

Most importantly, you do not need to. The default schema will be the same as the default schema of the user, which is dbo by default. You don't need to write out the schema if it's the same as the user's (and since you obviously don't know about schemas at all, it quite likely is :)). So just use [MyTable] instead of [dbo].[MyTable], and you'll be fine. In any case, if writing [dbo]. is so much trouble, maybe you should have a look at other ways to improve your code :) I've started writing the fully qualified name a long time ago and never looked back since.

[Roger Wolf]

What do you call "prefix", exactly?

[JAN]

@Shaikh Farooque: I do , actually , did . You can look at my answer below for the solution .

[TomTom]

@ron you mean the wrong answer that does not answer the question?

[JAN]

@TomTom: Yes , indeed

[Leonardo]

This is not a answer to the question. This will not "remove" the "dbo" (schema). it will merely alter it. Please either change the question or the answer.

[Nanosynth]

This doesn't work in SQL SRV 2017.All the boxes are grey and non-editable.The reason I need to get rid of the .dbo is because I imported in a DB2 database and out of all the tables that came with it, one was messed up and the data didn't come with it. It wouldn't let me edit the column attributes for the one column that was the problem because when I went to save it it said no and that Id have to drop the table. No big deal, I created another one and the naming convention of all 50+ tables is DB2INST1.WHATEVER and the new table is now dbo.DB2INST1.WHATEVER. I dont want that dbo there.