SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
hlsc1983
hlsc1983
SSC Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
Points: 291 Visits: 345
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?
sql crazy kid
sql crazy kid
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 243
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
hlsc1983
SSC Veteran
SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)SSC Veteran (291 reputation)

Group: General Forum Members
Points: 291 Visits: 345
i am using SSMS to create and execute my queries..am not sure if that answers your question.
sql crazy kid
sql crazy kid
SSC-Enthusiastic
SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)SSC-Enthusiastic (124 reputation)

Group: General Forum Members
Points: 124 Visits: 243
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
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39374 Visits: 38547


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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87347 Visits: 45272
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
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86117 Visits: 41096
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
below86
below86
SSC Eights!
SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)SSC Eights! (927 reputation)

Group: General Forum Members
Points: 927 Visits: 2440
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
liteswitch
liteswitch
SSC-Enthusiastic
SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)SSC-Enthusiastic (172 reputation)

Group: General Forum Members
Points: 172 Visits: 598
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87347 Visits: 45272
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search