SQL脚本开发总结~sql 增删主键

-------------------------------------------------------------------/*更新主键*/

begin try

declare @defname varchar(100)  

declare @cmd varchar(500)  

declare @tablename varchar(100)  

declare @keyname varchar(100)  

declare @keyColumn varchar(100)  

 

Set @tablename='你的表名' 

Set @keyname='你的新主键' --需要設置的key,分隔  

set @keyColumn='新主键数据定义'------------------例如'DepartmentRelationID nvarchar(50) default(NEWID())'---出现多个逗号隔开

 

select @defname= name  

   FROM sysobjects so   

   JOIN sysconstraints sc  

   ON so.id = sc.constid  

   WHERE object_name(so.parent_obj) = @tablename  

   and xtype='PK' 

 

if @defname is not null  

begin  

select @cmd='alter table '+ @tablename+ ' drop constraint '+ @defname  

--print @cmd  

   exec (@cmd)  

 end

begin

 set @defname='PK_'+@tablename   

 

select @cmd='alter table '+ @tablename+ ' ADD  '+@keyColumn+' constraint '+ @defname +' PRIMARY KEY('+@keyname+')'  

   exec (@cmd) 

   exec('sys.sp_addextendedproperty @name=N''MS_Description'', @value=N''关系标识'' , @level0type=N''SCHEMA'',@level0name=N''dbo'', @level1type=N''TABLE'',@level1name=N'''+@tablename+''', @level2type=N''COLUMN'',@level2name=N'''+@keyname+'''')

print '修改'+@tableName+'主键成功'

end

end try

begin catch

print ERROR_MESSAGE()

end catch

Go---

---------------------------------------------------------------------------------------------------------------