Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

User tables in master DB

Status
Not open for further replies.

pvmurphy

Programmer
Jul 29, 2003
50
US
One of our master databases seems to be populated with various user tables, plus the regular system tables. Everything seems to be working OK, but I’m not a 100% confident of it. How could this possible happened, but more important, how do I go about fixing the problem?

Many thanks,
 
Happens quite frquently when a bunch of users have access to SQL Server - LOL.

They could be using Query Analyzer and not properly choosing the DB they want, via the drop down box or by using a USE statement, to add table(s) to.

I would first try to identify who ownes the tables, maybe a user name is given. Then you must figure out if it was a user or a 3rd party app or tool that created these tables.

I would send out an E to all users who may use SQL Server that you are going to try and fix the issue. Then I would "RENAME" a table at a time to see what breaks and who hollers. If you are able to identify who ones it and what its purpose is, you could create another DB, move the table(s) and have your table owner rework their code to point to the new DB.

Just an idea!


Thanks

J. Kusch
 
I think this might have happened when we moved databases to a new server; all of the tables are our ‘standard’ user tables. All of these user tables in Master are also in their respective user databases, and they are getting updated properly. So would it be safe just to drop the unwanted tables in master? I printed out all the tables in master and compared to another server, and all of our system tables are there.

Many thanks,

Jim
 
I would first rename them as I suggested. Then if nothing breaks and all is well, you could copy them to a temp DB for a little while longer to be safe, and then delete them whenever you feel comfortable.

Have you check to see if there may be data in the tables residing in the Master DB that may not be in the tables of the respective DBs.

Thanks

J. Kusch
 
Jay,

I will follow your advise, thanks for all the help.

Jim
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top