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 (Command)
SELECT @TSQL;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
END;
CLOSE PKcursor;
DEALLOCATE PKcursor;
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- DROP CLUSTERED KEYS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');
/***************************************************************************************************************
*
* CREATE NONCLUSTERED PKEYS
*
* http://social.technet.microsoft.com/wiki/contents/articles/2321.script-to-create-or-drop-all-primary-keys.aspx
*
* NOTE: Subsection where script determines if PKEY or not was modified to only create NONCLUSTERED pkeys
*
***************************************************************************************************************/
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE NONCLUSTERED PKEYS --''');
--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'
+ ' NONCLUSTERED'
+ ' (';
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 (Command)
SELECT @TSQL;
FETCH NEXT FROM PKcursor INTO @object_id, @parent_object_id;
END;
CLOSE PKcursor;
DEALLOCATE PKcursor;
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE NONCLUSTERED PKEYS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');
/***************************************************************************************************************
*
* CREATE FOREIGN KEYS
*
* http://stackoverflow.com/questions/159038/can-foreign-key-constraints-be-temporarily-disabled-using-t-sql
*
***************************************************************************************************************/
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE FOREIGN KEY CONSTRAINTS --''');
INSERT INTO #workTable (Command)
SELECT 'ALTER TABLE ' + const.parent_obj + '
ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
' + const.parent_col_csv + '
) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')'
FROM (
SELECT QUOTENAME(fk.NAME) AS [const_name]
,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
FROM sys.foreign_key_columns AS fcP
WHERE fcp.constraint_object_id = fk.object_id
FOR XML path('')
), 1, 1, '') AS [parent_col_csv]
,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
,STUFF((
SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
FROM sys.foreign_key_columns AS fcR
WHERE fcR.constraint_object_id = fk.object_id
FOR XML path('')
), 1, 1, '') AS [ref_col_csv]
FROM sys.foreign_key_columns AS fkc
INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
GROUP BY fkc.parent_object_id
,fkc.referenced_object_id
,fk.NAME
,fk.object_id
,schParent.NAME
,schRef.NAME
) AS const
ORDER BY const.const_name
INSERT INTO #workTable (Command) VALUES ('PRINT ''-- CREATE FOREIGN KEY CONSTRAINTS -- COMPLETE'' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)');
SET NOCOUNT OFF;
/***************************************************************************************************************
*
* Iterate through Work Table on the database
*
***************************************************************************************************************/
--SELECT * FROM #workTable
DECLARE @Command VARCHAR(MAX)
DECLARE WorkTableCursor CURSOR
FOR
SELECT Command
FROM #workTable
OPEN WorkTableCursor
FETCH NEXT FROM WorkTableCursor
INTO @Command
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Command
EXECUTE (@Command)
FETCH NEXT FROM WorkTableCursor
INTO @Command
END
CLOSE WorkTableCursor
DEALLOCATE WorkTableCursor
DROP TABLE #workTable
PRIMARY KEY
, you can sayNONCLUSTERED
.