22,209
社区成员
发帖
与我相关
我的任务
分享
declare ap scroll cursor for
select name from sysobjects where xtype='U' and name like 'uld%'
declare @tab varchar(200),@sql varchar(5000)
open ap
fetch first from ap into @tab
while(@@FETCH_STATUS<>-1)
begin
select @sql='alter table ['+@tab+'] add constraint ['
+name+'] '
from sys.objects
where parent_object_id=object_id(@tab) and [type]='PK'
select @sql=@sql+' primary key('+
(select cast(
(select name+','
from (select distinct c.name
from sys.indexes a
inner join sys.index_columns b
on a.[object_id]=b.[object_id]
inner join sys.columns c
on b.[object_id]=c.[object_id]
and b.index_column_id=c.column_id
where a.[object_id]=object_id(@tab)
and a.is_primary_key=1) t
for xml path('')) as varchar(20)))
select @sql=left(@sql,len(@sql)-1)+') '
-- 打印
print @sql
fetch next from ap into @tab
end
close ap
deallocate ap
-- 建测试表
create table houyichong
(id1 int not null,
id2 int not null,
de varchar(10)
constraint pk_houyichong primary key(id1,id2)
)
-- 动态产生脚本
declare @tab varchar(200),@sql varchar(5000)
select @tab='houyichong'
select @sql='alter table ['+@tab+'] add constraint ['
+name+'] '
from sys.objects
where parent_object_id=object_id('houyichong')
select @sql=@sql+' primary key('+
(select cast(
(select name+','
from (select c.name
from sys.indexes a
inner join sys.index_columns b
on a.[object_id]=b.[object_id]
inner join sys.columns c
on b.[object_id]=c.[object_id]
and b.index_column_id=c.column_id
where a.[object_id]=object_id(@tab)
and a.is_primary_key=1) t
for xml path('')) as varchar(20)))
select @sql=left(@sql,len(@sql)-1)+') '
-- 打印
print @sql
--> 结果
alter table [houyichong] add constraint [pk_houyichong] primary key(id1,id2)
-- 执行
exec(@sql)