删除数据库所有表数据（保留表结构和约束）

--独占数据库

ALTER DATABASE pr_dev_data SET OFFLINE WITH ROLLBACK IMMEDIATE 

--获得禁用所有外键约束的语句 ，执行 

select  'ALTER TABLE ['  + b.name +  '] NOCHECK CONSTRAINT ' +  a.name +';' as  禁用约束    
from  sysobjects  a ,sysobjects  b       
where  a.xtype ='f' and  a.parent_obj = b.id  

--删除

exec sp_msforeachtable 'truncate table ?'

--获得启用所有外键约束的语句  ，执行

select  'ALTER TABLE [' + b.name +  '] CHECK CONSTRAINT ' +  a.name +';' as  启用约束      
from  sysobjects  a ,sysobjects  b       
where  a.xtype ='f' and  a.parent_obj = b.id  

 

 

--启用获得所有启用语句，禁用：NOCHECK
SELECT 'alter table ['+OBJECT_SCHEMA_NAME(object_id)+'].['+OBJECT_NAME(object_id)+'] CHECK CONSTRAINT all'  FROM sys.tables WHERE is_ms_shipped=0;

--获得所有删除表数据语句
SELECT 'DELETE FROM ['+OBJECT_SCHEMA_NAME(object_id)+'].['+OBJECT_NAME(object_id)+']  '  FROM sys.tables WHERE is_ms_shipped=0;

 

--解除独占

alter database pr_dev_data set  online