1、删除表的外键约束
CREATE PROCEDURE sp_drop_all_fk
as
declare @sql varchar(255)
declare dropsql_cursor cursor for
select 'alter table '+object_name(fkeyid)+' drop constraint '+object_name(constid)+char(10) from sysreferences
open dropsql_cursor
fetch dropsql_cursor into @sql
begin tran
while @@fetch_status=0
begin
execute(@sql)
if @@error <> 0
begin
rollback
return
end
fetch dropsql_cursor into @sql
end
deallocate dropsql_cursor
commit
GO
exec sp_drop_all_fk -- 执行存储过程
2、删除表
declare @table varchar(400)
while (select count(*) from sysobjects where type='u')>=1
begin
set @table=(select top 1 name from sysobjects where type='u')
set @table='drop table '+@table
exec(@table)
end
select name,type from sysobjects where type='u'
3、如果没有约束:
sp_MSforeachtable @command1 = "TRUNCATE TABLE ?"