Solved

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

Posted on 2010-09-08
5
562 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

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

Suggested Solutions

Title # Comments Views Activity
CREATE DATABASE ENCRYPTION KEY 1 64
Restrict result set 1 38
INSERT DATE FROM STRING COLUMN 18 56
Help  needed 3 23
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 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