我是ACCESS导入到SQL中,表很多
但绝大部分表中的主键列名都是id3
救一次性把这些设置为主键的代码
如果能做到列名id3不存在,就设置type3为主键,那更好.
------解决方案--------------------
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 [email protected])
exec ( 'alter table [ '[email protected]+ '] add constraint PK_ '[email protected]+ '_ '[email protected]+ ' primary key clustered([ '[email protected]+ ']) ')
fetch next from curPK into @TabName
end
close curPK
deallocate curPK
------解决方案--------------------
up
------解决方案--------------------
学习！！
------解决方案--------------------
需要先查出所有的表然后循环调用小楼的！嘿嘿
------解决方案--------------------
up 学习
------解决方案--------------------
//借用一下
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 [email protected])
exec ( 'alter table [ '[email protected]+ '] add constraint PK_ '[email protected]+ '_ '[email protected]+ ' primary key clustered([ '[email protected]+ ']) ')
fetch next from curPK into @TabName
end
close curPK
deallocate curPK
------解决方案--------------------
我是ACCESS导入到SQL中,表很多
但绝大部分表中的主键列名都是id3
救一次性把这些设置为主键的代码
如果能做到列名id3不存在,就设置type3为主键,那更好.
----
这种需求,我建议用程序来完成,在程序中判断你的各种情况,然后对数据做相应的处理.