Update SYSOBJECTS SET ID = ID
报错。不允许对系统目录进行即席更新
网上找的方案。EXEC sp_configure 'allow updates', '1'
--下面这句不需要执行,因为默认的是1
EXEC sp_configure 'show advanced option', '1'
--下面的这句要执行,否则它只有等到重启时才会生效
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'max degree of parallelism' ,'1'
RECONFIGURE WITH OVERRIDE
GO
Update SysObjects Set ID = ID
go
SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE但是不行。要实现的功能是批量删除约束。或者有大侠知道如何使用SP批量删除约束也可以。
报错。不允许对系统目录进行即席更新
网上找的方案。EXEC sp_configure 'allow updates', '1'
--下面这句不需要执行,因为默认的是1
EXEC sp_configure 'show advanced option', '1'
--下面的这句要执行,否则它只有等到重启时才会生效
RECONFIGURE WITH OVERRIDE
EXEC sp_configure 'max degree of parallelism' ,'1'
RECONFIGURE WITH OVERRIDE
GO
Update SysObjects Set ID = ID
go
SP_CONFIGURE 'ALLOW UPDATES', 0
RECONFIGURE WITH OVERRIDE但是不行。要实现的功能是批量删除约束。或者有大侠知道如何使用SP批量删除约束也可以。
然后用游标实现
--1.删除外键约束
DECLARE c1 cursor for
select 'alter table ['+ object_name(parent_obj) + '] drop constraint ['+name+']; '
from sysobjects
where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
begin
exec(@c1)
fetch next from c1 into @c1
end
close c1
deallocate c1
--2.删除表
DECLARE c2 cursor for
select 'drop table ['+name +']; '
from sysobjects
where xtype = 'u'
open c2
declare @c2 varchar(8000)
fetch next from c2 into @c2
while(@@fetch_status=0)
begin
exec(@c2)
fetch next from c2 into @c2
end
close c2
deallocate c2
--批量清除表内容:
--1.禁用外键约束
DECLARE c1 cursor for
select 'alter table ['+ object_name(parent_obj) + '] nocheck constraint ['+name+']; '
from sysobjects
where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
begin
exec(@c1)
fetch next from c1 into @c1
end
close c1
deallocate c1
--2.清除表内容
DECLARE c2 cursor for
select 'truncate table ['+name +']; '
from sysobjects
where xtype = 'u'
open c2
declare @c2 varchar(8000)
fetch next from c2 into @c2
while(@@fetch_status=0)
begin
exec(@c2)
fetch next from c2 into @c2
end
close c2
deallocate c2
--3.启用外键约束
DECLARE c1 cursor for
select 'alter table ['+ object_name(parent_obj) + '] check constraint ['+name+']; '
from sysobjects
where xtype = 'F'
open c1
declare @c1 varchar(8000)
fetch next from c1 into @c1
while(@@fetch_status=0)
begin
exec(@c1)
fetch next from c1 into @c1
end
close c1
deallocate c1
我有用游标实现的。
CREATE Procedure [dbo].[DropColumn]
@TableName varchar(50),
@ColumnName varchar(50)
As
Begin
Declare @TableID int,@ColumnID int
Select @TableID = ID From SysObjects Where Name = @TableName
IF @TableID > 0
Begin
Select @ColumnID = ColID From SysColumns Where Name = @ColumnName And ID = @TableID
IF @ColumnID > 0
Begin
Declare @Name varchar(50)
Declare Curs CurSor For
Select Name From SysObjects Where ID IN (
Select ConstID From Sysconstraints Where ID = @TableID And ColID = @ColumnID
)
Open Curs
Fetch Next From Curs Into @Name
While @@Fetch_Status=0
Begin
Exec('Alter Table '+@TableName+' DROP '+@Name)
Fetch Next From Curs Into @Name
End
Close Curs
Deallocate Curs
Exec('Alter Table '+@TableName+' DROP Column '+@ColumnName)
End
End
return 0
End但是语句太多了。