how to differentiate between two tables sharing the same name but belonging to different databases

Question

how to differentiate between two tables sharing the same name but belonging to different databases

hlsc1983

SSCommitted

Points: 1971
More actions
January 12, 2014 at 6:15 am

#302630

i have a database called 'sims' . i made a copy of it and called it 'simscopy'. both have tables that share the same name. for eg: the table 'students' is common. how can i make sure that when i execute sql queries on 'students' of 'simscopy' , the wrong table is not referred to?

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

sql crazy kid Ten Centuries Points: 1388 More actions · Answer 1

Can you please re-frame your question..

if you are connecting by using connection string via application,you will not be having issues . Or if you are talking about in query analyser, if you set default database as sims , you can ensure you are always connected to right db.

hlsc1983 SSCommitted Points: 1971 More actions · Answer 2

i am using SSMS to create and execute my queries..am not sure if that answers your question.

sql crazy kid Ten Centuries Points: 1388 More actions · Answer 3

You can set a default database in user login level .Run below system proc to set your default database .

Exec sp_defaultdb @loginame='ur Login', @defdb='sims'

This sets by default database as sims when 'ur Login' login to ssms . Then you don't need to change database from the context box.

I am not sure this is the right solution you are looking for.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

USE Sims

go

select * from dbo.Students where StudentID = 1; -- Will select data from Students table in database Sims

go

USE SimsCopy

go

select * from dbo.Students where StudentID = 1; -- Will select data from Students table in database SimsCopy

go

Does this help? If not, please elaborate on what you are asking.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Use three-part naming for your queries

SELECT <column list> FROM Sims.dbo.Students

SELECT <column list> FROM SimsCopy.dbo.Students

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 6

GilaMonster (1/12/2014)
Use three-part naming for your queries
SELECT <column list> FROM Sims.dbo.Students
SELECT <column list> FROM SimsCopy.dbo.Students

I agree that works great for ad hoc queries (and you already know this... just saying it for others that may read this) but I'd stick with the 2 part naming convention in any stored T-SQL and use differently named synonyms to point to other database just in case they rename or even move that other database. It keeps you from having to find all occurances of 3 part naming in all of the code and changing it. It's much easier to just change the synonym.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

below86 SSChampion Points: 11513 More actions · Answer 7

Jeff Moden (1/12/2014)
GilaMonster (1/12/2014)
Use three-part naming for your queries
SELECT <column list> FROM Sims.dbo.Students
SELECT <column list> FROM SimsCopy.dbo.Students
I agree that works great for ad hoc queries (and you already know this... just saying it for others that may read this) but I'd stick with the 2 part naming convention in any stored T-SQL and use differently named synonyms to point to other database just in case they rename or even move that other database. It keeps you from having to find all occurances of 3 part naming in all of the code and changing it. It's much easier to just change the synonym.

Sorry Jeff I have to agree with Gail here. ALWAYS use the three-part naming. It's going to make the query easier to read and you won't run into an issue of connecting/using the wrong table. I've seen it happen here time and time again.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

liteswitch SSCommitted Points: 1824 More actions · Answer 8

I'd actually disagree with putting the database name in the 3 identifier, if the database gets renamed all the queries will fail, and that's a lot of effort to put right.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

The only time I'll use three-part naming is when I know that I need to be crossing databases. Otherwise 2-part naming's fine.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

below86 SSChampion Points: 11513 More actions · Answer 10

liteswitch (1/13/2014)
I'd actually disagree with putting the database name in the 3 identifier, if the database gets renamed all the queries will fail, and that's a lot of effort to put right.

I guess from my stand point if someone renamed a database I would want things to fail.

If you're digging through thousands of lines of code and the connection was to 'databaseA' but somewhere in the code someone put a 'use databaseB' and you are looking for the table that needs 'fixing' you may look at the wrong table.

I've ran into many issues here where the wrong tables get deleted or cleared out because someone didn't put all 3 parts.:w00t:

I can convert the way I code for a lot of different things, but this is one of those things I don't think I will budge on.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 11

GilaMonster (1/13/2014)
The only time I'll use three-part naming is when I know that I need to be crossing databases. Otherwise 2-part naming's fine.

I agree with you Gail. Sprocs that span multiple databases are always an issue if/when they get moved around and/or renamed. There is not much that can be done other than making sure there is sufficient documentation indicating those objects that reference across to other databases.

There is no easy around that one.

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

below86 SSChampion Points: 11513 More actions · Answer 12

Almost ever query we write will span more than one database, so that's why I prefer it.:-)

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 13

Kurt W. Zimmerman (1/13/2014)
GilaMonster (1/13/2014)
The only time I'll use three-part naming is when I know that I need to be crossing databases. Otherwise 2-part naming's fine.
I agree with you Gail. Sprocs that span multiple databases are always an issue if/when they get moved around and/or renamed. There is not much that can be done other than making sure there is sufficient documentation indicating those objects that reference across to other databases.
There is no easy around that one.
Kurt

Actually, there is. Use synonyms instead of hard coding the 3 or 4 part naming into your code.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 14

below86 (1/13/2014)
Jeff Moden (1/12/2014)
GilaMonster (1/12/2014)
Use three-part naming for your queries
SELECT <column list> FROM Sims.dbo.Students
SELECT <column list> FROM SimsCopy.dbo.Students
I agree that works great for ad hoc queries (and you already know this... just saying it for others that may read this) but I'd stick with the 2 part naming convention in any stored T-SQL and use differently named synonyms to point to other database just in case they rename or even move that other database. It keeps you from having to find all occurances of 3 part naming in all of the code and changing it. It's much easier to just change the synonym.
Sorry Jeff I have to agree with Gail here. ALWAYS use the three-part naming. It's going to make the query easier to read and you won't run into an issue of connecting/using the wrong table. I've seen it happen here time and time again.

Then I'll have to agree to disagree. I've never had that problem when using synonyms.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)