日期:2014-05-18  浏览次数:20759 次

谁能帮我写个代码,自动建立主键
我是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 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

------解决方案--------------------
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 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
------解决方案--------------------
我是ACCESS导入到SQL中,表很多
但绝大部分表中的主键列名都是id3
救一次性把这些设置为主键的代码
如果能做到列名id3不存在,就设置type3为主键,那更好.

----

这种需求,我建议用程序来完成,在程序中判断你的各种情况,然后对数据做相应的处理.