sp_rename packs a lot of magic, but there are some things it can't do.

I worried that if I renamed a table in T-SQL that the keys for joins would get jacked up. It looks like sp_rename takes care of that problem under the hood as part of what it does, but there is another problem yet that does require manual clean up and consideration. Let me illustrate. In this silly example, a bridge table called Shores straddles Lands and Lakes:

CREATE TABLE Lands
(
   LandId int IDENTITY NOT NULL,
   Land varchar(50) NOT NULL
) ON [PRIMARY]
ALTER TABLE Lands ADD CONSTRAINT
   PK_LandId PRIMARY KEY CLUSTERED
   (
      LandId
   ) WITH (
      STATISTICS_NORECOMPUTE = OFF,
      IGNORE_DUP_KEY = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON
   ) ON [PRIMARY]
 
Insert INTO Lands (Land) VALUES ('Indiana')
Insert INTO Lands (Land) VALUES ('Michigan')
Insert INTO Lands (Land) VALUES ('Minnesota')
Insert INTO Lands (Land) VALUES ('New York')
Insert INTO Lands (Land) VALUES ('Ohio')
Insert INTO Lands (Land) VALUES ('Ontario')
Insert INTO Lands (Land) VALUES ('Pennsylvania')
Insert INTO Lands (Land) VALUES ('Wisconsin')
 
CREATE TABLE Lakes
(
   LakeId int IDENTITY NOT NULL,
   Lake varchar(50) NOT NULL
) ON [PRIMARY]
ALTER TABLE Lakes ADD CONSTRAINT
   PK_LakeId PRIMARY KEY CLUSTERED
   (
      LakeId
   ) WITH (
      STATISTICS_NORECOMPUTE = OFF,
      IGNORE_DUP_KEY = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON
   ) ON [PRIMARY]
 
Insert INTO Lakes (Lake) VALUES ('Erie')
Insert INTO Lakes (Lake) VALUES ('Huron')
Insert INTO Lakes (Lake) VALUES ('Michigan')
Insert INTO Lakes (Lake) VALUES ('Ontario')
Insert INTO Lakes (Lake) VALUES ('Superior')
 
CREATE TABLE Shores
(
   ShoreId int IDENTITY NOT NULL,
   LandId int NOT NULL,
   LakeId int NOT NULL
) ON [PRIMARY]
ALTER TABLE Shores ADD CONSTRAINT
   PK_ShoreId PRIMARY KEY CLUSTERED
   (
      ShoreId
   ) WITH (
      STATISTICS_NORECOMPUTE = OFF,
      IGNORE_DUP_KEY = OFF,
      ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON
   ) ON [PRIMARY]
ALTER TABLE Shores ADD CONSTRAINT
   FK_LandId FOREIGN KEY
   (
      LandId
   ) REFERENCES Lands (
      LandId
   ) ON UPDATE NO ACTION ON DELETE NO ACTION
ALTER TABLE Shores ADD CONSTRAINT
   FK_LakeId FOREIGN KEY
   (
      LakeId
   ) REFERENCES Lakes (
      LakeId
   ) ON UPDATE NO ACTION ON DELETE NO ACTION

 
 

If we stick an entry into the Shores table with say 7 for LandId and 1 for LakeId that should represent the shore of Lake Erie at Erie, Pennsylvania. Get it? I know, it's silly. Anyways, what if we turn around and rename Lakes to GreatLakes like this?

EXEC sp_rename 'Lakes', 'GreatLakes';
EXEC sp_rename 'GreatLakes.LakeId', 'GreatLakeId', 'COLUMN'
EXEC sp_rename 'GreatLakes.Lake', 'GreatLake', 'COLUMN'

 
 

Well, if we try to add an entry it will work just fine as, again, the key wire-ups are not undermined. They are also not renamed however as the names for constraints are just kept in magic strings and could have been anything, without any imposed convention, to begin with. Observe:

 
 

What should be done? Drop and recreate all the tables, slurping data off to temporary tables first? No, of course not. You should just rename the restraints with sp_rename.