一、将主键聚集属性去除，改为非聚集
首先需要在sys.Indexes表中进行查找
set @sjgsTable='R'+@xmdm +'SJGS'
set @ryTable ='R'+ @xmdm+'RY'
--由于主键BM带有聚集属性，先要将该聚集属性进行取消后才能设置ryid的聚集属性
--查找表的主键(可能有多个，ry和sjgs套表只有一个
--首先查找ry套表的主键，找到后取消聚集属性,设置为非聚集
set @tmpSql=N'SELECT @pk_nameOUT=name FROM sys.indexes WHERE is_primary_key=1 and object_id = OBJECT_ID(N'''+'[dbo].'+@ryTable+''')'
--print @tmpSql
SET @PatmDefinition=N'@pk_nameOUT nvarchar(100) OUTPUT'
exec sp_executesql @tmpSql,@PatmDefinition,@pk_nameOUT=@pk_name OUTPUT
--print 'pkry=' + @pk_name
if @pk_name <>''
begin
set @tmpSql='ALTER TABLE '+@ryTable+' DROP CONSTRAINT '+ @pk_name
--print @tmpSql
exec (@tmpSql)
--将ry表设置为非聚集属性
set @tmpSql='alter table '+@ryTable+' add constraint pk_'+@ryTable+' primary key nonclustered(BM)'
--print @tmpSql
exec (@tmpSql)
end
二、创建索引
--如果当前没有建立RYID的索引就创建
set @tmpSql=N'SELECT @indexCountOut=count(*) FROM sys.indexes WHERE name=''IX_RYID'' and object_id = OBJECT_ID(N'''+'[dbo].'+@sjgsTable+''')'
SET @PatmDefinition=N'@indexCountOut nvarchar(100) OUTPUT'
exec sp_executesql @tmpSql,@PatmDefinition,@indexCountOut=@indexCount OUTPUT
if @indexCount=0
begin
set @tmpSql='Create CLUSTERED INDEX IX_RYID ON '+@sjgsTable + '(RYID)'
exec (@tmpSql)
end
