清空数据库中的所有数据

在我们的开发中，有时会碰到清空一个数据库中所以数据的情况，但直接用delete 或者truncate 语句是不行的，因为其中的约束阻止了我们的操作。
所以我们可以分四步骤来实现：
1、得到表的所以约束，把它放到一个临时表中
2、删除所以约束。
3、使用delete 或truncate 清空数据。
4、把约束重新加到表上。
Use DatabaseName --Temporary table to hold constraints info most of the time at a different location
                 -- or database
--This could be a temp table however set as static

IF  EXISTS (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')
 truncate table T_FK_Xref
go
--Create Table to store constraint information
 IF  NOT EXISTS  (Select [name] from sys.tables where [name] = 'T_FK_Xref' and type = 'U')
 Create table DatabaseName.dbo.T_FK_Xref (
 ID int identity (1,1),
 ConstraintName varchar (255),
 MasterTable varchar(255),
 MasterColumn varchar(255),
 ChildTable varchar(255),
 ChildColumn varchar(255),
 FKOrder int
 )
go
--Store Constraints
insert into DatabaseName.dbo.T_FK_Xref(ConstraintName,MasterTable,MasterColumn,ChildTable,ChildColumn,FKOrder)
  SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable
      ,sc2.name MasterColumn
      ,object_name(fkeyid) ChildTable
      ,sc1.name ChildColumn
      ,cast (sf.keyno as int) FKOrder
   FROM sysforeignkeys  sf
INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
ORDER BY rkeyid,fkeyid,keyno

go

use databaseName --Database to removed constraints
go
---Ready to remove constraints

declare @ConstraintName varchar (max) -- Name of the Constraint
declare @ChildTable varchar (max) -- Name of Child Table
declare @MasterTable varchar (max)--Name of Parent Table
declare @ChildColumn varchar (max)--Column of Child Table FK
declare @MasterColumn varchar (max)-- Parent Column PK
declare @FKOrder smallint -- Fk order
declare @sqlcmd varchar (max) --Dynamic Sql String


-- Create cursor to get constraint Information
declare drop_constraints cursor 
fast_forward
for
SELECT object_name(constid) as ConstraintName,object_name(rkeyid) MasterTable
      ,sc2.name MasterColumn
      ,object_name(fkeyid) ChildTable
      ,sc1.name ChildColumn
      ,cast (sf.keyno as int) FKOrder
   FROM sysforeignkeys  sf
INNER JOIN syscolumns sc1 ON sf.fkeyid = sc1.id AND sf.fkey = sc1.colid
INNER JOIN syscolumns sc2 ON sf.rkeyid = sc2.id AND sf.rkey = sc2.colid
ORDER BY rkeyid,fkeyid,keyno

open drop_constraints
fetch next from drop_constraints
into
@ConstraintName
,@MasterTable
,@MasterColumn
,@ChildTable
,@ChildColumn
,@FKOrder
while @@Fetch_status = 0
begin

-- Create Dynamic Sql to drop constraint

 select @sqlcmd = 'alter table '+@ChildTable+' drop constraint '+@ConstraintName--+' foreign key '+'('+@ChildColumn+')'+' references '+@MasterTable+' ('+@MasterColumn+')'+' on delete no action on update no action'
If EXISTs (select object_name(constid) from sysforeignkeys where object_name(constid) = @ConstraintName)
    exec (@sqlcmd)
fetch next from drop_constraints
into
@ConstraintName
,@MasterTable
,@MasterColumn
,@ChildTable
,@ChildColumn
,@FKOrder
end
close drop_constraints
deallocate drop_constraints

go
----------------------------------------------------------------
我们也可以用微软不公开的存储过程来实现此操作，这就非常简单了，代码如下：
--Removed CHECK Constraint-------------------------
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' --NOCHECK Constraints
print 'All Constraints Disable'
go

--truncate All tables  if trying to empty the database
 --- Ensure the T_X_ref database is located on a different database

-------------  Truncate All Tables from Model ----------------
-----To limit tables a table with sub model tables must be created  and used joins-----
EXEC sp_MSForEachTable 'truncate TABLE ? '
print 'All tables truncated'
go