Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to differentiate between two tables sharing the same name but belonging to different databases Expand / Collapse
Author
Message
Posted Sunday, January 12, 2014 6:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 4:06 AM
Points: 20, Visits: 83
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?
Post #1530094
Posted Sunday, January 12, 2014 7:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 7:40 AM
Points: 47, Visits: 151
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.
Post #1530095
Posted Sunday, January 12, 2014 7:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 11, 2014 4:06 AM
Points: 20, Visits: 83
i am using SSMS to create and execute my queries..am not sure if that answers your question.
Post #1530096
Posted Sunday, January 12, 2014 7:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 7:40 AM
Points: 47, Visits: 151
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.
Post #1530098
Posted Sunday, January 12, 2014 8:08 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:31 PM
Points: 22,510, Visits: 30,231

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.



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)
Post #1530099
Posted Sunday, January 12, 2014 8:35 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
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 2008, MVP
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

Post #1530105
Posted Sunday, January 12, 2014 10:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:13 PM
Points: 35,969, Visits: 30,261
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1530123
Posted Monday, January 13, 2014 7:34 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: 2 days ago @ 9:47 AM
Points: 214, Visits: 722
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.
Post #1530294
Posted Monday, January 13, 2014 8:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, April 08, 2014 6:47 AM
Points: 69, Visits: 557
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.

Post #1530302
Posted Monday, January 13, 2014 8:09 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:59 AM
Points: 41,530, Visits: 34,447
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 2008, MVP
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

Post #1530304
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse