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

来源：互联网发布：手机记工时软件编辑：程序博客网时间：2024/06/10 03:44

--独占数据库

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

0 0