Solved

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

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pivot Query Problem 9 47
Sql Query join multiple table and distinct records 7 31
INSERT DATE FROM STRING COLUMN 18 59
Help Required 2 39
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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

840 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