I have create a stored procedure what do the job for you.
first we have to remove all foreign keys otherwise we can't drop the primairy key constraint.
Alter that we loop to the keys drop and recreate them
Create procedure proc_changepk
As
--first drop all references
declare @sql nvarchar(max)
declare cursRef cursor for
SELECT
'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) +
'].[' + OBJECT_NAME(parent_object_id) +
'] DROP CONSTRAINT [' + name + ']' as ref
FROM sys.foreign_keys
open cursRef
fetch next from cursRef into @sql
while @@fetch_status = 0 begin
exec(@sql)
fetch next from cursRef into @sql
end
close cursRef
deallocate cursRef
--drop and recreate primairy keys
declare @pktable table (constraintname nvarchar(255),tablename nvarchar(255),colname nvarchar(255))
insert into @pktable(constraintname,tablename,colname)
SELECT CONSTRAINT_NAME,TABLE_NAME,COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
declare @pkname nvarchar(255),@tablename nvarchar(255),@cols nvarchar(255)
declare Mycurs cursor for
--maybe more than one col for primairy key
SELECT
p.constraintname,p.tablename,
STUFF((SELECT '. ' + colname from @pktable where constraintname=p.constraintname
FOR XML PATH('')), 1, 1, '') [cols]
FROM @pktable p
GROUP BY constraintname,tablename
open mycurs
fetch next from mycurs into @pkname,@tablename,@cols
while @@fetch_status = 0 begin
--drop key
set @sql='alter table ' + @tablename + ' drop CONSTRAINT [' + @pkname + ']'
print @sql
exec(@sql)
--create key
set @sql='alter table ' + @tablename + ' add CONSTRAINT [pk_' + @tablename + '] primary key NONCLUSTERED (' + ltrim(@cols) + ')'
print @sql
exec(@sql)
fetch next from mycurs into @pkname,@tablename,@cols
end
close MyCurs
deallocate MyCurs
GO
