when I tried to create a table, it is using my ID as the schema name. But when the table is created on my manager's computer, the schema name is "dbo". Why? He might have administrator rights. When I tried to rename by using sp_rename, it is not changing.
I wish I could combine the answers of three people here, because the answer really is in three parts, and each part has been answered quite well. So I'll mark this as community wiki.
The reason you are getting a different schema other than
dbois because your default schema is not
dbo, and you are writing:
CREATE TABLE foo(...);
When you do this, SQL Server assumes you want to create the object in your default schema (part of the purpose of having a custom default schema, after all).
To stop this from happening, always specify the schema prefix when creating or referencing objects. When you write the statement this way, there is no way for SQL Server to get it wrong:
CREATE TABLE dbo.foo(...); CREATE TABLE your_schema.foo(...);
To move a table that was accidentally created in the wrong schema, you can use
ALTER SCHEMA. For example, if you created
your_schema.fooand you want it in the
dboschema, you can say:
IF NOT EXISTS ( SELECT 1 FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.[schema_id] = s.[schema_id] WHERE t.name = N'foo' AND s.name = N'dbo' ) BEGIN ALTER SCHEMA [dbo] TRANSFER your_schema.foo; END ELSE BEGIN PRINT 'Sorry, couldn''t move the object because it already exists.'; END