-- Script to Generate Drop to go with the adds in the post
-- From web resource: http://connectsql.blogspot.com/2009/07/script-to-create-all-primary-and.html
-- For TSQL Syntax see http://msdn.microsoft.com/en-us/library/ms190273.aspx
/*

EXEC sp_Opti_PKConstraints_DROP
@TableName = 'Widget'

*/

ALTER PROC sp_Opti_PKConstraints_DROP
	@TableName VARCHAR(128) = NULL

AS

DECLARE @DoPrimaryOnly BIT
SET @DoPrimaryOnly = 1
DECLARE cPK CURSOR FOR

SELECT DISTINCT C.TABLE_NAME, C.CONSTRAINT_NAME , F.NAME 
FROM SYS.INDEXES I
INNER JOIN SYS.FILEGROUPS F
ON I.DATA_SPACE_ID = F.DATA_SPACE_ID
INNER JOIN SYS.ALL_OBJECTS O
ON I.[OBJECT_ID] = O.[OBJECT_ID]
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON O.NAME = C.TABLE_NAME
WHERE (1=1)
AND	C.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND C.Table_Name = @TableName
ORDER BY C.TABLE_NAME

DECLARE @PkTable SYSNAME
DECLARE @PkName SYSNAME
DECLARE @FileName SYSNAME
DECLARE @IfExists VARCHAR(2000)

-- Loop through all the primary keys
OPEN cPK
FETCH NEXT FROM cPK INTO @PkTable, @PkName , @FileName
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @PKSQL NVARCHAR(4000) SET @PKSQL = ''

SET @IfExists = 
		'' + CHAR(13) 

--########################## DROP ##############################

	SET @PKSQL = @IfExists +  CHAR(13) + CHAR(13) + '-- ' + @PkTable			+ CHAR(13) +
	
	'IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = '	+ CHAR(13) +
	'OBJECT_ID(N''[dbo].[' + @PkTable + ']'' ' + ' '			+ CHAR(13) +
	'AND name = N''' + @PkName + ''')' + ' '					+ CHAR(13) +
	'BEGIN'														+ CHAR(13) +
	'~~Caution	ALTER TABLE [dbo].' + @PkTable + ' '					+ CHAR(13) + 
	'	~~DROP CONSTRAINT ' + @PkName								+ CHAR(13) + 
	'END' + ' '													+ CHAR(13) 

--########################## / DROP ############################

-- For the drop, we're skipping the columns


SET @PKSQL = ~~@PKSQL + ')' + CHAR(13) + 
' ON '+@FileName
-- Print the primary key statement
PRINT @PKSQL

FETCH NEXT FROM cPK INTO @PkTable, @PkName, @FileName
END
CLOSE cPK
DEALLOCATE cPK

EndProcessing: