EXEC sp_MsForeachtable N'ALTER TABLE ? NOCHECK CONSTRAINT ALL';EXEC sp_MsForeachtable
@command=N'TRAUNCATE TABLE ?',
@whereand=N' and o.name NOT IN(''xxx'',''xxx'')';EXEC sp_MsForeachtable N'ALTER TABLE ? CHECK CONSTRAINT ALL';--这样试试.
@command=N'TRAUNCATE TABLE ?',
@whereand=N' and o.name NOT IN(''xxx'',''xxx'')';EXEC sp_MsForeachtable N'ALTER TABLE ? CHECK CONSTRAINT ALL';--这样试试.
INSERT tb SELECT 1,2
UNION ALL SELECT 2,4
UNION ALL SELECT 3,5;CREATE TABLE tc(id int,val int
CONSTRAINT FK_ID FOREIGN KEY(id) REFERENCES tb(id));
INSERT tc SELECT 1,10
UNION ALL SELECT 3,30;
GOEXEC sp_MsForeachtable N'ALTER TABLE ? NOCHECK CONSTRAINT ALL';EXEC sp_MsForeachtable
@command1=N'DELETE ?',
@whereand=N' and o.name NOT IN(''tba'')';EXEC sp_MsForeachtable N'ALTER TABLE ? CHECK CONSTRAINT ALL';--查看数据
SELECT * FROM tb;
SELECT * FROM tc;GO
DROP TABLE tc,tb;
select 'alter table '+name+' nocheck constraint all' from sysobjects where type='U'2)删除所有表数据的SQL
select 'TRUNCATE TABLE '+name from sysobjects where type='U'3)恢复所有表约束的SQL
select 'alter table '+name+' check constraint all' from sysobjects where type='U'