Click here to monitor SSC
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
below86
below86
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 2111
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
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1396
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
below86
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 2111
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
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44790 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44790 Visits: 39845
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is usually not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4458 Visits: 9813
below86 (1/13/2014)
Almost ever query we write will span more than one database, so that's why I prefer it.:-)


If your system consists of multiple databases - and there is never the possibility that one or more of those databases grows to a point where it needs to be moved to another server, then 3-part naming makes sense.

However, if the possibility exists that at some point in time one or more of those databases can be moved to a different server - then using synonyms makes sense.

In either case, if someone changes the name of an object - drops an object - etc... then both will generate a failure.

Synonyms are ideal if you have linked servers in your organization. On a test system, you would have linked server 'OtherSystemTest' and on the production system your linked server would be 'OtherSystem'. If you are using 4-part naming in your code - then promoting that code to production *requires* a code change be made as it is being applied.

Using synonyms in this situation avoids any issues with making code changes between test and production and still allows for code comparisons between the environments.

Synonyms are also ideal in situations where the names of the databases on a test system are different than the names in production. For example, some sites like to put a post-fix of _test on the database names in their test environment. In that situation, using 3-part naming doesn't work because you cannot promote that code to production without code changes - however, using synonyms means no changes to the code need to be made.

There are good reasons to use either method - and both are valid.

Note: I use both methods, but will only use 3-part naming when referencing objects outside the scope of the database where the procedure will live. This allows me to move that procedure to another database if needed - without worrying about accessing data in the 'old' database.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

j-1064772
j-1064772
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 1202
What would prevent a user from scripting all stored procs and views in a common file and then just use a text editor to find and re-place the old-new databasename and rerun the scripts after dropping all the stored procs and views (you will of course create a backup before starting any of it) ?

So why should it be a lot of work for someone who has the authority to rename or relocate a databse ?

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