| Author |
Topic |
|
albertkohl
Aged Yak Warrior
740 Posts |
 Posted - 2007-10-23 : 15:58:40
|
how can i put a table into the system tables folder?for example, i have a table called zipinfo that i use off of every database i have. instead of always having to say select zip from residential.dbo.zipinfo i'd like to use be able to select * from zipinfo i notice if i do this with system tables, it works, EX: select * from sysfiles i can run from ANY database.can anyone point me in the right direction? (same thing for stored procedures |
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-23 : 16:19:30
|
| But the info in the system tables is different in each database though correct? So in essence, they aren't the same table. They have the same table name, but have different data in the table. Why would you want to put zipinfo in each database? That means you'll have multiple copies of that table to maintain. When info in zipinfo is added, updated or deleted, you'll have to do it to each database not just residential. It would be very very bad to try to put it in each database. |
 |
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-10-23 : 16:25:00
|
| no, the table is always the same. it's more of a reference. but right now i have to either use the 3-part naming, or i have to have a copy in every database. i'd just like one copy, and only have to run the select with onepart naming. maybe i'm just being lazy though :o) |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-23 : 16:31:04
|
| It's best to leave it in one place. As I said, just because a table is in every database (the system tables) doesn't mean it's the same table. It just happens to have the same name...but with different data. Someone else here might be able to point out some other reasons as well. |
|
|
|
albertkohl
Aged Yak Warrior
740 Posts |
Posted - 2007-10-23 : 16:39:33
|
| oic what you mean. okay, so basically i should probably stick to the 3part naming then?cool, thanks. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-23 : 16:40:42
|
| Yea. Will save you alot of trouble in the future most likely. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-10-23 : 16:45:02
|
| You could add a view to each database that references that table using three-part naming thus avoiding using tree-part naming everyplace except your view. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-23 : 16:48:42
|
| Yea, that's true...didn't even think of that. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-23 : 17:10:07
|
| The OP did mention that they wanted to do this for Stored Procs too. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-10-23 : 18:22:13
|
| I'm currently at a location where they reference objects in other DBs on the same server using three-part naming like you are doing. And I can tell you that I would seriously consider avoiding such an architecture (or at least have a very good rational for doing so) unless you are going to have one Main database for centralized objects, like the ZipInfo table. It is very difficult if not impossible to script out and build a database because every database references an object in every other database. (At least they have not gone with linked servers and bumped up to four-part naming. yeah?). So the dependency chain is a royal pain in the behind. I understand wanting to do some form of encapsulation, but you need to be smart about it. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-10-24 : 09:44:01
|
| Yea, it's best to keep it all in one database for many reasons. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-10-24 : 09:58:51
|
If you want this tables and SP's to be in all NEW databases, put them in model database.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
|
universal table