mssql sqlserver 删除数据库中所有主键的方法分享


摘要:
下文通过举例的方式讲述删除数据库中所有主键的方法分享,如下所示:
实验环境:sql server 2008 R2



实现思路:
下文借助系统表sysobjects,遍历出所有主键名称和主键所属表名称,然后生成删除主键的sql脚本,
通过动态执行删除脚本,来达到删除主键的目的,如下所示

declare @PkName varchar(256);
declare @TableName varchar(256);
declare cursor1 cursor for 
Select b.name as TableName a.name as PkName from sysobjects a JOIN sysobjects b ON a.parent_obj = b.[id] where a.xtype='PK';
open cursor1 
fetch next from cursor1 into @TableName,@PkName
while @@fetch_status=0 
begin
exec('Alter table '+@TableName+' drop '+ @PkName) --删除主键sql脚本
print 'Alter table '+@TableName+' drop '+ @PkName   --打印sql脚本
fetch next from cursor1 into @TableName,@PkName
end
close cursor1 ;
deallocate cursor1;