3

Someone renamed our Users table to SYSUsers, which is a key sql table name. Is there a way to rename this table?

I've tried right-clicking the table and going to rename, and running sp_RENAME on it, but both are trying to rename the system sysusers table instead of the user-created one. I can't even select or export data from the [MyDatabase].[dbo].[SYSUsers] since it reads from the sql server sysusers table instead of the user-created one.

We're using SQL Server 2005.

Rachel
  • 130,264
  • 66
  • 304
  • 490
  • "Someone renamed our Users table to SYSUsers" - are they roasting over a large fire? How did they have the permissions to do that? Make another space over that fire! – Mitch Wheat Apr 06 '11 at 14:23
  • That is not the correct casing of the table name though. Presumably in a case sensitive collation you wouldn't get that issue. – Martin Smith Apr 06 '11 at 14:25
  • lol the table is for software that isn't in production yet, so its not a day-ruining mistake. I just don't want to have to recreate all the foreign keys that point to the users table since there are a lot, and I'd prefer to get my dummy data back. – Rachel Apr 06 '11 at 14:27
  • 1
    @Rachel - Well if its not in production one workaround might be to temporarily change the DB collation to CS rename the table then switch back. – Martin Smith Apr 06 '11 at 14:30
  • @Martin do you know the set of scripts to do that? I am looking for it now, but so far all I see is scripts to change table collation – Rachel Apr 06 '11 at 14:31
  • 1
    @Rachel - It's the database default collation I was suggesting. `ALTER DATABASE [YourDB] COLLATE SQL_Latin1_General_CP1250_CS_AS` (or any other case sensitive collation) I work on a case sensitive instance so I can't test this would work. I can confirm that creating a table called `SYSUsers` caused me no problems however but doing it all in lower case does! – Martin Smith Apr 06 '11 at 14:36
  • Actually though I just created a case insensitive database and a table called `SYSUsers` caused no problems there either. I think it must go off the collation of the system databases and my suggestion won't work in that case. – Martin Smith Apr 06 '11 at 14:43
  • 1
    @Martin Thanks, that worked. If you post it as an Answer I'll accept it – Rachel Apr 06 '11 at 14:52
  • @Rachel - Excellent! Good job they didn't use all lower case then - not sure if there would be any way round that. – Martin Smith Apr 06 '11 at 14:57

1 Answers1

2

SYSUsers doesn't cause any such problem for me because I work on a case sensitive instance (however creating a table called sysusers does!).

Perhaps you could try temporarily altering the collation of the database to a CS one and switch it back after?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    Thanks Martin. For others who encounter this issue, I ended up running `SELECT SERVERPROPERTY('COLLATION')` to get the original Collation value, storing that somewhere and running `ALTER DATABASE MyDatabase COLLATE SQL_Latin1_General_CP1250_CS_AS`, ran my rename statements, then ran the `ALTER DATABASE` statement again with the old Collation value – Rachel Apr 06 '11 at 15:07