Script to automate converting a clustered index on primary key (with foreign keys to it) column to non clustered?

What's a good way to easily convert a clustered index on a primary key column to a nonclustered index? I know how to do this manually, but it's time consuming if there are several tables which have foreign keys to the primary key as they need to be dropped and recreated.

Looking for a script or free tool to automate this.

I am using SQL Server 2012

Well, luckily for you, I did this very thing a few months back... I just never actually published it to my blog... Guess I should get to that at some point, but until then, here you go:

```
/*
*
* This Script will perform the following operations:
* 1) Drop All RI
* 2) Drop All PKEYS
* 3) Recreate PKEYS as NONCLUSTERED
* 4) Recreate ALL RI
*
* To do this it will create a work table and populate it with the commands to execute then iterate through
* the work table to execute pregenerated commands.
*
*/

CREATE TABLE #workTable
(
CommandID INT IDENTITY(1,1),
Command VARCHAR(MAX)
)

SET NOCOUNT ON;

/*
*
* DROP FOREIGN KEYS
*
* http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql
*
*/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP FOREIGN KEY CONSTRAINTS --''');

INSERT INTO #workTable (Command)
SELECT 'ALTER TABLE [' + sch.NAME + '].[' + OBJECT_NAME(fk.parent_object_id) + ']' + ' DROP CONSTRAINT ' + '[' + fk.NAME + ']'
FROM sys.foreign_keys AS fk
INNER JOIN sys.schemas AS sch ON sch.schema_id = fk.schema_id
ORDER BY fk.NAME

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP FOREIGN KEY CONSTRAINTS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');

/*
*
* DROP CLUSTERED PKEYS
*
* http://social.technet.microsoft.com/wiki/contents/articles/2321.script-to-create-or-drop-all-primary-keys.aspx
*
*/

INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP CLUSTERED KEYS --''');

DECLARE @object_id int;
DECLARE @parent_object_id int;
DECLARE @TSQL NVARCHAR(4000);
DECLARE @COLUMN_NAME SYSNAME;
DECLARE @is_descending_key bit;
DECLARE @col1 BIT;
DECLARE @action CHAR(6);

SET @action = 'DROP';
--SET @action = 'CREATE';

DECLARE PKcursor CURSOR FOR
select kc.object_id, kc.parent_object_id
from sys.key_constraints kc
inner join sys.objects o
on kc.parent_object_id = o.object_id
where kc.type = 'PK' and o.type = 'U'
and o.name not in ('dtproperties','sysdiagrams') -- not true user tables
order by QUOTENAME(OBJECT_SCHEMA_NAME(kc.parent_object_id))
,QUOTENAME(OBJECT_NAME(kc.parent_object_id));

OPEN PKcursor;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;

WHILE @@FETCH_STATUS = 0
BEGIN
IF @action = 'DROP'
SET @TSQL = 'ALTER TABLE '
+ QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
+ ' DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
ELSE
BEGIN
SET @TSQL = 'ALTER TABLE '
+ QUOTENAME(OBJECT_SCHEMA_NAME(@parent_object_id))
+ '.' + QUOTENAME(OBJECT_NAME(@parent_object_id))
+ ' ADD CONSTRAINT ' + QUOTENAME(OBJECT_NAME(@object_id))
+ ' PRIMARY KEY'
+ CASE INDEXPROPERTY(@parent_object_id
,OBJECT_NAME(@object_id),'IsClustered')
WHEN 1 THEN ' CLUSTERED'
ELSE ' NONCLUSTERED'
END
+ ' (';

DECLARE ColumnCursor CURSOR FOR
select COL_NAME(@parent_object_id,ic.column_id), ic.is_descending_key
from sys.indexes i
inner join sys.index_columns ic
on i.object_id = ic.object_id and i.index_id = ic.index_id
where i.object_id = @parent_object_id
and i.name = OBJECT_NAME(@object_id)
order by ic.key_ordinal;

OPEN ColumnCursor;

SET @col1 = 1;

FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@col1 = 1)
SET @col1 = 0
ELSE
SET @TSQL = @TSQL + ',';

SET @TSQL = @TSQL + QUOTENAME(@COLUMN_NAME)
+ ' '
+ CASE @is_descending_key
WHEN 0 THEN 'ASC'
ELSE 'DESC'
END;

FETCH NEXT FROM ColumnCursor INTO @COLUMN_NAME, @is_descending_key;
END;

CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;

SET @TSQL = @TSQL + ');';

END;

INSERT INTO #workTable (

StackExchange Database Administrators Q#68170, answer score: 5