How to get default schema when adding a table

Question

How to get default schema when adding a table

Coreysan 1,811

I have an internal database, and when I add a new table, I get a problem:

when a record is written to INFORMATION_SCHEMA.TABLES, the column "TABLE_SCHEMA" has the wrong

default schema. Instead of the default "dbo", it is adding my login domain path and name. like this:

"Mydomain\MyName".

Is there a way to reset this to write "dbo"?

Anonymous

2023-07-26T08:09:39.76+00:00

Hi @Coreysan

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Accepted answer

4 additional answers

Your answer

Anonymous

2023-07-26T08:09:39.76+00:00

Hi @Coreysan

We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Answer 1

Anonymous

Hi @Coreysan

As a supplement, this is the official documentation for altering a schema.

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-schema-transact-sql?view=sql-server-ver16

Best regards,

Percy Tang

Answer 2

Yitzhak Khabinsky 26,586

Hi @Coreysan,

It is very simple.

ALTER SCHEMA dbo TRANSFER [DOMAIN\user].yourtable;

ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName;

Answer 3

Olaf Helper 47,436

the column "TABLE_SCHEMA" has the wrong default schema

It's neither the "default" schema nor a wrong one, it's the schema where you created the object/table.

It's the schema where you create the table and if you don't define explicit the schema (already a failure), then the default schema of the surrent user is used.

Answer 4

Erland Sommarskog 121.4K MVP Volunteer Moderator

If you say CREATE TABLE MyTable, the table will be created in your default schema. Which may be dbo or something else. You can review your default schema with this query:

SELECT default_schema_name FROM sys.database_principals WHERE name = USER

To change the default schema, you can use the ALTER USER command.

Coreysan 1,811 Reputation points

2023-07-21T00:09:01.3766667+00:00

@Erland Sommarskog

When I query with this:

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'mytable'

I get two (2) records, where one has a table_schema of 'dbo' and the other with a table_schema of 'MyDomain\MyName'.

I have never seen that before!!!
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-21T07:41:21.48+00:00

Some time has to be the first!

And if you just say "CREATE TABLE MyTable" and don't pay attention to your default schema that can happen.

Yes, these days, the default schema for most users is dbo. However, up to SQL 2000, the default schema for a user was always the same as the username. In fact, in those days, we did not even talk about schemas, but called that thing in the middle of a three-part "owner", because schema and owner was the same in those days.

When you create a user with CREATE USER, the user will by default get dbo as the default schema, but if you use any of the older commands like sp_adduser, they will behave in the old SQL 2000 way.

As janed says, you avoid this problem if you say CREATE TABLE dbo.MyTable.

Answer 5

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

How to get default schema when adding a table

4 additional answers

Your answer