Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mkswanson
Starting Member
21 Posts |
 Posted - 2008-06-24 : 13:19:09
|
| We are using SQL 2005 to drive a database application. All of the tables are supposed to be owned by user1, so the full table names should be user1.table1, user1.table2, etc.We have a situation where we have duplicate tables:dbo.table1user1.table1dbo.table2user1.table2dbo.table3user1.table3This has always been strange, but hasn't really caused many issues.Part of the application is querying a table called "TOTALS" and the query isn't retunring any data. dbo.TOTALS is empty (since it shouldn't exist) and user.TOTALS has all of our information. Since nothing is being returned by the select query that is just being run against "TOTALS," I would assume it is actually running against dbo.TOTALS.How can I force SQL to runSELECT * (actually complex criteria, but * should work for this purpose)FROM TOTALSto actually run against user1.TOTALS without changing the query since that can't be altered?Additionally, is there an effecient way to delete all of the dbo owned tables (this database has several hundred tables)? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-06-24 : 13:22:20
|
| 1. Try setting the default schema to user1.2. SELECT 'DROP TABLE ' + TABLE_NAMEFROM INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = 'dbo'For future databases that you might work on, it's so much easier to use dbo objects. So when creating objects, specify dbo.ObjectName. When calling the objects, you could specify dbo.ObjectName or just ObjectName.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2008-06-24 : 13:34:26
|
| SELECT * FROM user1.TOTALS |
|
|
|
mkswanson
Starting Member
21 Posts |
Posted - 2008-06-24 : 13:37:09
|
| Thanks for the quick response.I unfortunately do not have any control of the way the application was written - it is a third party application, and their standard installation requires the "user1" schema.I am having a problem changing the default schema to "user1." What am I misisng? How should I be doing this?Sorry for the dumb question, and thanks for your help! |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-06-24 : 19:34:58
|
| <<I am having a problem changing the default schema to "user1." >>What have you tried?Try changing db owner using sp_changedbownerMadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
Duplicate Tables