7

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 ?

Community
  • 1
  • 1
JAN
  • 21,236
  • 66
  • 181
  • 318
  • Do you have any situation? Because it is useful from performance prospective. – Shaikh Farooque Sep 04 '14 at 11:10
  • 3
    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" .... – marc_s Sep 04 '14 at 11:16
  • 5
    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. – Luaan Sep 04 '14 at 11:25
  • What do you call "prefix", exactly? – Roger Wolf Sep 04 '14 at 12:15
  • @Shaikh Farooque: I do , actually , did . You can look at my answer below for the solution . – JAN Sep 04 '14 at 13:59
  • @ron you mean the wrong answer that does not answer the question? – TomTom Sep 04 '14 at 14:24
  • @TomTom: Yes , indeed – JAN Sep 04 '14 at 14:54

2 Answers2

3

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
JAN
  • 21,236
  • 66
  • 181
  • 318
  • 2
    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. – Leonardo Sep 04 '14 at 14:03
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.

Roya Kianbakht
  • 21
  • 2
  • 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. – Nanosynth May 16 '20 at 20:58