22,301
社区成员




create table tt(C int,A int,B int)
sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
update syscolumns
set colid=(select count(*) from syscolumns a where a.name<=syscolumns.name and a.id=syscolumns.id)
where id=object_id('tt')
sp_configure 'allow updates',0
RECONFIGURE WITH OVERRIDE
select * from tt
/*
A B C
----------- ----------- -----------
(所影响的行数为 0 行)
*/
--遍历表
go
sp_configure 'allow updates',1
go
RECONFIGURE WITH OVERRIDE
go
declare @SQL varchar(4000)
declare @TableName varchar(30)
declare sTableName Cursor for select [name] from sysobjects where xtype='u' and name<>'dtproperties'
Open sTableName
fetch next from sTableName into @TableName
while @@fetch_status=0
begin
--更新
set @SQL='update syscolumns set colid=(select count(*) from syscolumns a where a.name<=syscolumns.name and a.id=syscolumns.id),colorder=(select count(*) from syscolumns a where a.name<=syscolumns.name and a.id=syscolumns.id) where id=object_id('''+@TableName+''')'
print(@SQL)
exec(@SQL)
fetch next from sTableName into @TableName
end
close sTableName
deallocate sTableName
go
sp_configure 'allow updates',0
go
RECONFIGURE WITH OVERRIDE
--遍历表
go
sp_configure 'allow updates',1
go
RECONFIGURE WITH OVERRIDE
go
declare @SQL varchar(4000)
declare @TableName varchar(30)
declare sTableName Cursor for select [name] from sysobjects where xtype='u' and name<>'dtproperties'
Open sTableName
fetch next from sTableName into @TableName
while @@fetch_status=0
begin
--更新
set @SQL='update syscolumns set colid=(select count(*) from syscolumns a where a.name<=syscolumns.name and a.id=syscolumns.id) where id=object_id(''cis.'+@TableName+''')'
print(@SQL)
exec(@SQL)
fetch next from sTableName into @TableName
end
close sTableName
deallocate sTableName
go
sp_configure 'allow updates',0
go
RECONFIGURE WITH OVERRIDE