| Author |
Topic |
|
dila4425
Starting Member
8 Posts |
 Posted - 2006-04-18 : 14:44:51
|
| Hi, while I'm trying to do Select statements, is there a way/setting whereby I don't have to constantly prefix the table names with a prefix?For example, I'm working in multiple environments and they're differentiated by the prefix in their table names, uat.** vs prod.**Any help would be appreciated.Please email me at dickson.lau@rogers.comThanks again.Dickson |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-18 : 14:47:42
|
| Is the prefix the user name, the database name or an alias?Tara Kizeraka tduggan |
 |
|
|
druer
Constraint Violating Yak Guru
314 Posts |
Posted - 2006-04-18 : 15:52:17
|
| The prefixes are strictly for your good, and can be dropped if the tables will evaluate properly. In Query Analyzer if your current database is "PROD" and you don't qualify your tables you will be using the tables in PROD. If you issue a USE TEST command before your queries, then your query would use those in TEST. The prefixes are necessary, and are handy for independence from the current default database, when you need to join tables from different environments. Then you would need to say select ... from test.dbo.table1 inner join prod.dbo.table2 etc.My understanding is that there is a little performance gain as well since the database doesn't have to try and resolve the actual table when it is fully pathed, but I don't think it is enough (at least in my experience) to warrant wearing yourself out fully qualifying the table names. If you didn't already know, you can fully qualify the table and then "ALIAS" it so that you can just refer to the alias in the rest of the query:select a.field1, b.field2from test.dbo.table1 a inner join prod.dbo.table2 b on b.field6 = a.field1where a.field9 = '5'and a.field10 < 14order by a.field1 |
|
|
|
dila4425
Starting Member
8 Posts |
Posted - 2006-04-18 : 16:01:42
|
| Thanks for your response.The prefix of Prod.** is simply the name of the environment where the data resides. But I had always thought as long as my drop-down was pointing to the right directory, that's where SQL would go to look.I really don't wish to keep prefixing every table with prod.** if I'm querying off the single enviornment and not linking to another dbase, as druer pointed out.Again, is there a setting I need to check off so I can select from GLTRANS as opposed to prod.GLTRANS? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-18 : 16:07:24
|
| I don't know what you mean by the name of the environment where the data resides. This doesn't mean anything in SQL Server. So is Prod the database name or the user name?Your connection string should put you in the right place so you don't need to prefix. But it is a best practice to qualify your objects.Tara Kizeraka tduggan |
|
|
|
dila4425
Starting Member
8 Posts |
Posted - 2006-04-18 : 16:18:08
|
| Sorry Tara. The Prod part is not referencing the user name.When I look at the Object Browser, under the server name, there's a directory "prod", then "User Tables", and I see all the tables with names like "prod.*** "Then when I reference these tables in my select statements, I must prefix the tables with prod.** or else the error message coming back is object not found. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-18 : 16:22:32
|
| So Prod is your database name then. Change your database in the drop down to Prod. Or run:USE ProdGOYou now won't need to prefix them. For your application (if you have one), the connection string will handle which database it will use.Tara Kizeraka tduggan |
|
|
|
dila4425
Starting Member
8 Posts |
Posted - 2006-04-18 : 16:29:19
|
| See, that's what I've been doing. I've been dropping down and selecting prod first. Then I run my selects...but that doesn't seem to fix this problem. That's why I thought there is a setting somewhere that I need to change in order for it to do this. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-18 : 16:32:46
|
| There is no setting. The drop down fixes your problem. When you get the error, verify that the drop down says Prod. Are your databases on different servers or all one SQL Server?Tara Kizeraka tduggan |
|
|
|
dila4425
Starting Member
8 Posts |
Posted - 2006-04-18 : 16:34:49
|
| one server.the drop down definitely says prod.can I email you a screenshot? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-18 : 16:36:11
|
| Yes. Send me an e-mail through this forum. I'll reply to it so that you can attach a file.Tara Kizeraka tduggan |
|
|
|
dila4425
Starting Member
8 Posts |
Posted - 2006-04-18 : 16:37:40
|
| Sorry, I'm getting an message saying it's down right now.Can you please send me an email to dickson.lau@rogers.com and I'll email you through there?Thanks.Dicksonps sorry for taking up all this time. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-04-18 : 16:53:13
|
| The problem is that both your database name and the owner of the object are called Prod. This means that the qualified object is named Prod.Prod.TableName. So you must use the two part naming convention at least, which is Prod.TableName. The drop down only fixes the first Prod, which is the database name. I'd recommend changing (sp_changeobjectowner) the owners of these objects to dbo to avoid this problem.Tara Kizeraka tduggan |
|
|
|
dila4425
Starting Member
8 Posts |
Posted - 2006-04-18 : 16:57:56
|
| Ok. Thanks for all your help, Tara.Dickson |
|
|
|
dila4425
Starting Member
8 Posts |
Posted - 2006-04-19 : 08:27:56
|
| Hi all,Out of curiosity, what are the advantages/disadvantages of reverting back to setting the objects owner back to dbo.** as opposed to something specific?Other than not having to always query with the additional object owner name...Dickson |
|
|
|
twhelan1
Yak Posting Veteran
71 Posts |
Posted - 2006-04-19 : 10:34:40
|
If you change owner to dbo you solve the issue with having to specify the owner name in querying as you've said (which can be of considerable benefit if you find yourself trying to guess who owns what table). Also, consider what would happen if a user who was a table owner were deleted, this gets very messy.The disadvantage of changing the owner name to dbo, all of your queries that have fully qualified names will be broken. This is particularly bad if you have a lot of users who have written Excel files or other saved queries that are hard coded rather than in views and stored procedures. If you're a mean old DBA who took over for a rather generous DBA that allowed his users to create their own tables, this can be particularly entertaining when you change ownership and wait for those users to complain that their queries no longer work  ~Travis |
|
|
|
|
dropping the prefix when querying tables