How to cascade rename PK on table, MS SQL 2005

Question

I have successfully been able to rename a table and drop all constraints on that table with foreign key relationships and build they all back up. However, now I am at a point where the PK_tblFoo exists in more than one place (when I transfer the table to another DB). Renaming the table does not rename the primary key.

How would I cascade rename the primary key? I have renamed the table, I just need to get this portion figured out.

Answer 1

I believe I will need to this manually, drop all FK constraints, run this guy:

IF EXISTS ( SELECT  *
        FROM    sys.indexes
        WHERE   object_id = OBJECT_ID(N'[dbo].[tblFoo]')
                AND name = N'PK_tblBusinessListings' ) 
ALTER TABLE [dbo].[tblFoo] DROP CONSTRAINT [PK_tblBusinessListings]
GO
ALTER TABLE [dbo].[tblFoo]
ADD CONSTRAINT [PK_tblFoo_1] PRIMARY KEY CLUSTERED ( [ListingID] ASC )
    WITH ( PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
           ONLINE = OFF ) ON [PRIMARY]

Then go through and set up all the FK constraint with the new PK name....errrgh....this is going to take a while.

Answer 2

You could also use a refactoring tool, I know Visual Studio Team Edition for Database Professionals could handle this.