Is there a quick way to change the keys and foreign keys of my database during the development stage with script

Question

Is there a quick script I can use in SQL Server to change the ID from int to unique identifier without physically going through and doing it manually. I have contemplated code gen to achieve this, but I would have thought using TSQL would be the quickest if possible that is!

Answer 1

I assume unique identifier you mean identity. If this is the case here is sample script:

 BEGIN TRANSACTION
 CREATE TABLE dbo.Tmp_MyTable
  (
  ID int NOT NULL IDENTITY (1, 1)
  )  ON [PRIMARY]
 GO
 SET IDENTITY_INSERT dbo.Tmp_MyTable ON
 GO
 IF EXISTS(SELECT * FROM dbo.MyTable)
   EXEC('INSERT INTO dbo.Tmp_MyTable (ID)
   SELECT ID FROM dbo.MyTable TABLOCKX')
 GO
 SET IDENTITY_INSERT dbo.Tmp_MyTable OFF
 GO
 DROP TABLE dbo.MyTable
 GO
 EXECUTE sp_rename N'dbo.Tmp_MyTable', N'MyTable', 'OBJECT'
 GO
 ALTER TABLE dbo.MyTable ADD CONSTRAINT
  PK_MyTable PRIMARY KEY CLUSTERED 
  (
  ID
  ) ON [PRIMARY]

 GO
 COMMIT

Answer 2

You could do it with a script.

ALTER TABLE x ADD COLUMN y uniqueIdentifier null
GO
UPDATE TABLE x SET y = NEWID() --Or NEWSEQUENTIALID() if its safe
GO
ALTER TABLE x ALTER COLUMN y uniqueIdentifier NOT NULL
GO
ALTER TABLE x DROP CONSTRAINT PK_PrimaryKeyConstraintName
GO
ALTER TABLE x DROP COLUMN PrimaryKeyColumn
GO
ALTER TABLE x ADD CONSTRAINT PK_NewPrimaryKeyConstraint PRIMARY KEY CLUSTERED 
( 
    y ASC 
)
GO

Note that this wont deal with your foreign key constraints and will break, if you have foreign key constraints this gets interesting, but I'm sure you can see the pattern.