Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

When to use database name 1

Status
Not open for further replies.

MrGandalf

Programmer
Jul 19, 2005
35
NL
Hi,

Can anyone tell me when to use database name in SQL?

For example:

Select * from zodata.client
OR
Select * from client

What is the difference? I get the error : Invalid object name client
 
if you want to use Table/View from different database then you have to use database name

[Example]
Use ProductionDB -- Change database to production

select * from CustomerDB..Customer -- Will select all rows from Customer table of CustomerDB Database


/* you can also create a view to point the table from different databse */

create view dbo.CustomerViewInProductionDB
as
select * from CustomerDB..Customer


[/Example]

for more detail look BOL (Books Online)



Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell
 
Tx. What if I'm in a stored procedure?

Must I then use the database name as a prefix? And must I use it also in the joins and where statements?
 

If your tables/views are in the same Database, where your SP residing then you dont have to use database name before Tables.

and IF YOUR TABLES/VIEWS ARE IN DIFFERENT DATABASE THEN YOU HAVE TO PREFIX THE DATABASE NAME.

Regards,


"There are no secrets to success. It is the result of preparation, hard work, and learning from failure." -- Colin Powell
 
By the way, the reason you got the error is that the proper syntax is:

Database.owner.table

If you own the table you can leave out the name, but the dots must still be there:

Database..table

-SQLBill

Posting advice: FAQ481-4875
 
Ah, that explains. I am using temporary tables. That means it's using temdb and my own database. It's clear. Tx, Sajid.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top