Solved

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

Posted on 2010-09-08
5
560 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
ID: 33627318
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
ID: 33627341
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
ID: 33627343
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
ID: 33627344
(or, what chapmandew said :) )
0
 
LVL 8

Assisted Solution

by:san_spy
san_spy earned 167 total points
ID: 33627452
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

910 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

23 Experts available now in Live!

Get 1:1 Help Now