Solved

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

Posted on 2010-09-08
5
567 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 143

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

688 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