Recreate primary keys as CLUSTERED (SQL Server 2008)

Let's say that you want to re-create all nonclustered PKs and make them clustered.
Here is stored procedure which will generate necessary DDLs.

Table that will store DDLs:

create table log_table 
(
 id integer identity,
 t text
);

Stored procedure:

IF OBJECT_ID ( 'sp_fk_pk', 'P' ) IS NOT NULL 
    DROP PROCEDURE sp_fk_pk;
GO

CREATE PROCEDURE sp_fk_pk
AS

-- Declare variables to store data returned by cursor
DECLARE 
 @childColumnList varchar(2000),
 @parentColumnList varchar(2000),
 @childSchema varchar(100),
 @childTable varchar(100),
 @fkName varchar(100),
 @parentSchema varchar(100),
 @parentTable varchar(100);

-- clean log table 
TRUNCATE TABLE log_table;


-- generate DDL to drop all foreign keys
INSERT INTO LOG_TABLE (t)
SELECT 
 'alter table ['+ SCHEMA_NAME(TB.schema_id)+'].['+OBJECT_NAME(F.PARENT_OBJECT_ID)+'] drop constraint ' + F.NAME  +';'
FROM SYS.FOREIGN_KEYS AS F
JOIN sys.tables AS TB ON F.parent_object_id = TB.object_id
JOIN sys.tables AS FT ON F.REFERENCED_OBJECT_ID = FT.object_id;



/**
  PRIMARY KEY 
 **/



-- declare the cursor to get all NONCLUSTERED indexes on primary keys
DECLARE dm_cursor_pk Cursor
FOR 
 select TABLE_SCHEMA, TABLE_NAME, name 
 from sys.indexes i, INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
 where name=tc.CONSTRAINT_NAME
 and type_desc='NONCLUSTERED';

-- open cursor
OPEN dm_cursor_pk 

-- iterate over recordset
FETCH NEXT from dm_cursor_pk 
INTO 
 @childSchema,
 @childTable,
 @fkName;
 
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF ( @@FETCH_STATUS <> -2 )

 SET @childColumnList = NULL;
 -- get list of columns that make PK
 select @childColumnList = coalesce(@childColumnList + ', ', '') + column_name
 from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
 where constraint_name=@fkName;
 
 -- insert DDL for index recreation into log table  
 INSERT INTO LOG_TABLE (T) VALUES ('create unique clustered index ' + @fkName + ' on [' + @childSchema + '].[' + @childTable + '](' + @childColumnList + ') with drop_existing;');

 -- iterate over recordset
 FETCH NEXT from dm_cursor_pk 
 INTO 
  @childSchema,
  @childTable,
  @fkName;   
END

-- close the cursor
CLOSE dm_cursor_pk

-- deallocate the space
DEALLOCATE dm_cursor_pk



/*
 FOREIGN KEYS CREATION 
 */
 
 
-- declare the cursor to get all foreign keys
DECLARE dm_cursor_fk Cursor
FOR 
SELECT 
 distinct(F.NAME)
FROM SYS.FOREIGN_KEYS AS F
JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID
JOIN sys.tables AS TB ON F.parent_object_id = TB.object_id
JOIN sys.tables AS FT ON F.REFERENCED_OBJECT_ID = FT.object_id;


-- open cursor
OPEN dm_cursor_fk 

-- iterate over recordset
FETCH NEXT from dm_cursor_fk 
INTO 
 @fkName;
 
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF ( @@FETCH_STATUS <> -2 )
 SET @childColumnList = NULL;
 SET @parentColumnList = NULL;

 -- get list of columns that make FK
 SELECT 
  @childSchema = SCHEMA_NAME(TB.schema_id),
  @childTable = OBJECT_NAME(F.PARENT_OBJECT_ID),
  @childColumnList = coalesce(@childColumnList + ', ', '') + COL_NAME(FC.PARENT_OBJECT_ID,FC.PARENT_COLUMN_ID),
  @parentSchema = SCHEMA_NAME(FT.schema_id),
  @parentTable = OBJECT_NAME (F.REFERENCED_OBJECT_ID),
  @parentColumnList = coalesce(@parentColumnList + ', ', '') + COL_NAME(FC.REFERENCED_OBJECT_ID,FC.REFERENCED_COLUMN_ID)
 FROM (SELECT * FROM SYS.FOREIGN_KEYS WHERE NAME=@fkName) AS F
 JOIN SYS.FOREIGN_KEY_COLUMNS AS FC ON F.OBJECT_ID = FC.CONSTRAINT_OBJECT_ID
 JOIN sys.tables AS TB ON F.parent_object_id = TB.object_id
 JOIN sys.tables AS FT ON F.REFERENCED_OBJECT_ID = FT.object_id
 ORDER BY SCHEMA_NAME(TB.schema_id), OBJECT_NAME(F.PARENT_OBJECT_ID);

 --  generate DDL to recreate FK
 INSERT INTO LOG_TABLE (T) VALUES ('alter table [' + @childSchema + '].[' + @childTable + '] ' 
     + ' add constraint ' + @fkName + ' foreign key (' + @childColumnList + ') references ' + '[' + @parentSchema + '].[' + @parentTable + '] ' 
     + '(' + @parentColumnList  + '); ');
 -- iterate over recordset
 FETCH NEXT from dm_cursor_fk 
 INTO 
  @fkName;
   
END

-- close the cursor
CLOSE dm_cursor_fk

-- deallocate the space
DEALLOCATE dm_cursor_fk


GO

Invocation of the procedure:

exec sp_fk_pk



Check the DDLs:

select t from log_table order by id


Note: use generated DDLs at your own risk

Sample schema:

create table a (
id integer not null,
t varchar(10)
);
alter table a add constraint pk_a primary key nonclustered(id);

create table b (
id integer not null,
a_id integer
);
alter table b add constraint pk_b primary key nonclustered(id);
alter table b add constraint fk_b_a foreign key (a_id) references a (id);

create table c (
id integer not null,
t varchar(10)
);
alter table c add constraint pk_c primary key nonclustered(id);

create table d (
id integer not null,
id1 integer not null,
t varchar(10)
);
alter table d add constraint pk_d primary key nonclustered(id,id1);


create table e (
id integer not null,
d_id integer,
d_id1 integer
);
alter table e add constraint pk_e primary key nonclustered(id);
alter table e add constraint fk_e_d foreign key (d_id,d_id1) references d (id,id1);