更改数据库中所有表,bh字段为主键:
这样写不对:
declare @name varchar(50)
declare @sql nvarchar(4000)
DECLARE abc_cursor CURSOR FOR
SELECT name from sysobjects where xtype='U'
open abc_cursor
fetch next from abc_cursor into @name
while @@FETCH_STATUS =0
begin
set @sql='ALTER TABLE ' + @name + ' ADD CONSTRAINT bh PRIMARY KEY CLUSTERED(bh)' exec sp_executesql @sql
fetch next from abc_cursor into @name
end
close abc_cursor
DEALLOCATE abc_cursor
请高手指点
这样写不对:
declare @name varchar(50)
declare @sql nvarchar(4000)
DECLARE abc_cursor CURSOR FOR
SELECT name from sysobjects where xtype='U'
open abc_cursor
fetch next from abc_cursor into @name
while @@FETCH_STATUS =0
begin
set @sql='ALTER TABLE ' + @name + ' ADD CONSTRAINT bh PRIMARY KEY CLUSTERED(bh)' exec sp_executesql @sql
fetch next from abc_cursor into @name
end
close abc_cursor
DEALLOCATE abc_cursor
请高手指点
可生成循环
declare @i int,@s varchar(8000)
SELECT id = identity(int,1,1),name into #t from sysobjects where xtype='U'
select @i=count(1) from #t
while @i>0
begin
select @s='alter table '+name+' alter column bh int not null' from #t where id=@i
exec(@s)
select @s='alter table '+name+' ADD CONSTRAINT bh PRIMARY KEY CLUSTERED(bh)' from #t where id=@i
exec(@s)
set @i=@i-1
end
declare @sql nvarchar(4000)
DECLARE abc_cursor CURSOR FOR
SELECT name from sysobjects where xtype='U'
open abc_cursor
fetch next from abc_cursor into @name
while @@FETCH_STATUS =0
begin
set @sql='alter table '+name+' alter column bh int not null'
exec(@sql)
set @sql='ALTER TABLE ' + @name + ' ADD CONSTRAINT bh PRIMARY KEY CLUSTERED(bh)' exec(@sql)
fetch next from abc_cursor into @name
end
close abc_cursor
DEALLOCATE abc_cursor
declare @sql nvarchar(4000)
declare @i int
set @i=1
DECLARE abc_cursor CURSOR FOR
SELECT name from sysobjects where xtype='U'
open abc_cursor
fetch next from abc_cursor into @name
while @@FETCH_STATUS =0
begin
set @sql='alter table '+name+' alter column bh int not null'
exec(@sql)
set @sql='ALTER TABLE ' + @name + ' ADD CONSTRAINT bh'+cast(@i as varchar)+' PRIMARY KEY CLUSTERED(bh)' exec(@sql)
set @i=@i+1
fetch next from abc_cursor into @name
end
close abc_cursor
DEALLOCATE abc_cursor
已在表 'Table2' 上定义了主键。
服务器: 消息 1750,级别 16,状态 1,行 1
未能创建约束。请参阅前面的错误信息。
服务器: 消息 4924,级别 16,状态 1,行 1
ALTER TABLE ALTER COLUMN 失败,因为列 'bh' 在表 'dtproperties' 中不存在。
----------
我怎么多一个表
一定会给你的 hrb2008() ( ) 信誉:100 Blog
dtproperties