When to use database name 1

[MrGandalf]

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

 

[SajidAttar]

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

 

[MrGandalf]

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?

 

[SajidAttar]

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

 

[SQLBill]

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

 

[MrGandalf]

Ah, that explains. I am using temporary tables. That means it's using temdb and my own database. It's clear. Tx, Sajid.