Solved

MSSQL 2005 database schema: what is the different between dbname.dbo and dbname

Posted on 2010-09-08
5
559 Views
Last Modified: 2013-11-05
hi all,

i have create a database with name "dbname" in MSSQL Server 2005.

as i use hibernate to manipulate the database, i was asked to tell the database schema.

in one computer i must use "dbname" as database schema, in another one i must use "dbname.dbo" as database schema.

i would like to know what is the different between dbname.dbo and dbname. how can i find which database schema is used in MSSQL Server?

thanks,

wantime
0
Comment
Question by:wantime
5 Comments
 
LVL 60

Expert Comment

by:chapmandew
Comment Utility
dbo is always the implied (default) schema for databases.  So, when you leave the schema out, dbo is used in its place.
0
 
LVL 9

Accepted Solution

by:
valkyrie_nc earned 167 total points
Comment Utility
dbo is the default schema assigned to tables. In SQL 2005 and on, you can create multiple schemas to help differentiate tables (for example, if they have different functions or belong to different applications).  

When the syntax "dbname.dbo.tablename" is used, it's equivalent to "dbname..tablename", which is what i think you're referring to above (usually, dbname.tablename, without another . to indicate the default schema, will fail).  When referring to a non-default schema, you'd used "dbname.schemaname.tablename".

hth

valkyrie_nc
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 166 total points
Comment Utility
to access a table, you can either specify (as from sql):.....so, please clarify if dbname really specifies the database name, or a schema.based on the login's permissions, the table's schema/owner, you might need or not need to specify it.if you need to specify the database name, that means that the login's default database is not pointing to the correct db, resp your connection does not specify the correct db.
0
 
LVL 9

Expert Comment

by:valkyrie_nc
Comment Utility
(or, what chapmandew said :) )
0
 
LVL 8

Assisted Solution

by:san_spy
san_spy earned 167 total points
Comment Utility
Basically the schema is associated to objects like tables,procedures and views. It is not associated to any database. As told above the default schema in SQL Server is dbo.

If you want to find the list of schemas existing in a database.

select * from sys.schemas will give the list of all available schemas in a particular database.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now