Hi there!

I've done already the schemas for all the tables a you suggested.

But when I created a new table the dbo prefix (dbo.ADM.NewTable) appears.

I'd like this dbo not to automatically appear whenever i created a new table as it requires me to alter whenever I created a new table.

Is this something that is required to determine the creator of the table?

Otherwise, I'd like that to remove.

Any help would be most appreciated...

Many thanks,

Cioden_sky

dbo is the default schema and you need to specify which schema you want to create the new object, such as:

CREATE TABLE guest.Table1 (ID INT)

Creates the Table1 table in the guest scheme.

• Edited by Maniaxe 2 hours 55 minutes ago

dbo is the default schema in SQL Server. You can create your own schemas to allow you to better manage your object namespace.

Actually you should leave those dbo. statements because your SQL will be faster since the optimizer doesn't have to lookup the schema. You can get more info here: Performance Impact of Procedure Calls without Owner Qualification

In SQL Server, you could have several schemas (the "dbo." thingie) with the same table name, e.g. dbo.MyTable, joe.MyTable, frank.MyTable.

If you then issue a SELECT (list of fields) FROM MyTable, SQL Server has to first figure out which of the "MyTable" tables you really mean --> this costs time, specifying right off the bat you want "dbo.MyTable" will SAVE you time.

OK, not a lot on a single query - but SELECT queries are QUITE frequent and it all adds up!

You can change a table in SQL Server that starts with a dbo prefix to one without. To do this, you can do the following:

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

dbo is the default schema and you need to specify which schema you want to create the new object, such as:

CREATE TABLE guest.Table1 (ID INT)

Creates the Table1 table in the guest scheme.

• Edited by Maniaxe Saturday, September 12, 2015 4:07 AM

dbo is the default schema and you need to specify which schema you want to create the new object, such as:

CREATE TABLE guest.Table1 (ID INT)

Creates the Table1 table in the guest scheme.

• Edited by Maniaxe Saturday, September 12, 2015 4:07 AM
• Marked as answer by Cioden_sky 18 hours 25 minutes ago

dbo is the default schema in SQL Server. You can create your own schemas to allow you to better manage your object namespace.

Actually you should leave those dbo. statements because your SQL will be faster since the optimizer doesn't have to lookup the schema. You can get more info here: Performance Impact of Procedure Calls without Owner Qualification

In SQL Server, you could have several schemas (the "dbo." thingie) with the same table name, e.g. dbo.MyTable, joe.MyTable, frank.MyTable.

If you then issue a SELECT (list of fields) FROM MyTable, SQL Server has to first figure out which of the "MyTable" tables you really mean --> this costs time, specifying right off the bat you want "dbo.MyTable" will SAVE you time.

OK, not a lot on a single query - but SELECT queries are QUITE frequent and it all adds up!

You can change a table in SQL Server that starts with a dbo prefix to one without. To do this, you can do the following:

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

• Marked as answer by Cioden_sky 18 hours 25 minutes ago

My guess is that you are using the table designer tool, which will add dbo as the schema. So when you *think* that you specify ADM as the schema and NewTable as the name, you in fact specify ADM.NewTable as the name and get dbo as the schema. This is how the designer tool work - you can't specify the schema. You get your default schema, period. So, only way to get the schema you want is to make that schema your default schema...

Hi Andrey,

I greatly appreciate your professional opinion and I am leaning towards it now (not removing the dbo.)

My only issues here is how to change back the table name with dbo. prefix?

Any codes suggestion.. appreciate further help on this.

Thanks,

Cioden_sky