declare @TabName sysname, @ColName sysname declare curPK cursor for select name from sysobjects a where xtype='U' and not exists (select 1 from sysobjects where parent_obj=a.id and xtype='PK') open curPK fetch next from curPK into @TabName while @@fetch_status=0 begin if exists (select 1 from syscolumns where id=object_id(@TabName) and name='id3') set @ColName='id3' else set @ColName='type3' if exists (select 1 from syscolumns where id=object_id(@TabName) and name=@ColName) exec ('alter table ['+@TabName+'] add constraint PK_'+@TabName+'_'+@ColName+' primary key clustered(['+@ColName+'])') fetch next from curPK into @TabName end close curPK deallocate curPK
//借用一下 declare @TabName sysname, @ColName sysname declare curPK cursor for select name from sysobjects a where xtype='U' and not exists (select 1 from sysobjects where parent_obj=a.id and xtype='PK') open curPK fetch next from curPK into @TabName while @@fetch_status=0 begin if exists (select 1 from syscolumns where id=object_id(@TabName) and name='id3') set @ColName='id3' else if exists (select 1 from syscolumns where id=object_id(@TabName) and name='type3') set @ColName='type3' else CONTINUE if exists (select 1 from syscolumns where id=object_id(@TabName) and name=@ColName) exec ('alter table ['+@TabName+'] add constraint PK_'+@TabName+'_'+@ColName+' primary key clustered(['+@ColName+'])') fetch next from curPK into @TabName end close curPK deallocate curPK
declare curPK cursor for select name from sysobjects a where xtype='U' and not exists (select 1 from sysobjects where parent_obj=a.id and xtype='PK')
open curPK
fetch next from curPK into @TabName
while @@fetch_status=0
begin
if exists (select 1 from syscolumns where id=object_id(@TabName) and name='id3')
set @ColName='id3'
else
set @ColName='type3'
if exists (select 1 from syscolumns where id=object_id(@TabName) and name=@ColName)
exec ('alter table ['+@TabName+'] add constraint PK_'+@TabName+'_'+@ColName+' primary key clustered(['+@ColName+'])')
fetch next from curPK into @TabName
end
close curPK
deallocate curPK
declare @TabName sysname, @ColName sysname
declare curPK cursor for select name from sysobjects a where xtype='U' and not exists (select 1 from sysobjects where parent_obj=a.id and xtype='PK')
open curPK
fetch next from curPK into @TabName
while @@fetch_status=0
begin
if exists (select 1 from syscolumns where id=object_id(@TabName) and name='id3')
set @ColName='id3'
else
if exists (select 1 from syscolumns where id=object_id(@TabName) and name='type3')
set @ColName='type3'
else
CONTINUE
if exists (select 1 from syscolumns where id=object_id(@TabName) and name=@ColName)
exec ('alter table ['+@TabName+'] add constraint PK_'+@TabName+'_'+@ColName+' primary key clustered(['+@ColName+'])')
fetch next from curPK into @TabName
end
close curPK
deallocate curPK
但绝大部分表中的主键列名都是id3
救一次性把这些设置为主键的代码
如果能做到列名id3不存在,就设置type3为主键,那更好.----这种需求,我建议用程序来完成,在程序中判断你的各种情况,然后对数据做相应的处理.