T-SQL语句实现清空数据库数据的代码,保留原有表形式和各种约束
/**********************************************************************/
----------------------清空数据库数据的SQL代码--------------
/**********************************************************************/
------------------------------------------------------
use TSZongHe_new --选择你要清空的数据库
-------------------------------------------------------
-------------禁用本库中所有表的外键约束-----
-------------------------------------------------------
DECLARE Employee_Cursor CURSOR FOR
select name from sysobjects where xtype='u' and (not name LIKE 'dtproperties') order by [name] asc;
declare @tablename varchar(300);
declare @str varchar(1000);
declare @rst int;
--SET @MyVariable = MyCursor
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
select @str = 'ALTER TABLE '+ @tablename + ' NOCHECK CONSTRAINT ALL';
EXECUTE(@str);
FETCH NEXT FROM Employee_Cursor into @tablename;
END
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
----------------------------------------------------
--------------删除本库中所有表数据----------
----------------------------------------------------
DECLARE Employee_Cursor CURSOR FOR
select name from sysobjects where xtype='u' and (not name LIKE 'dtproperties')
-------------------------------------------------------------------------------------------
--如果你想清空库中所有表的话,就不需要下段这部分代码
-------------------------------------------------------------------------------------------
and (name not in ('PageRights','ModuleInfo','RoleModuleRight','RoleInfo','UserInfo',
'PersonnelInfo','SystemDirectory','MarriageState','TechnicalPostInfo','EducationInfo',
'PlaceInfo ','DepartmentInfo ','FoodStandard','FieldsValue','PublicResShare','StyleInfo',
'DocumentTypeInfo','AddressKindInfo','PayTypeInfo','FoodStandard','CityInfo','HotelType',
'FootQuomodoInfo','HotelGradeInfo','PlaceArea','TravelAgencySortInfo','BusTypeInfo'))
--------------------------------------------------------------------------------------------
--如果你想保留某些表数据的话,可以加上下面这段代码,当然表名根据自己的情况写
--------------------------------------------------------------------------------------------
order by [name] asc;
declare @tablename varchar(300);
declare @str varchar(1000);
declare @rst int;
--SET @MyVariable = MyCursor
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
select @str = 'Delete '+ @tablename ;
EXECUTE(@str);
FETCH NEXT FROM Employee_Cursor into @tablename;
END
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
----------------------------------------------------
-------------恢复本库中所有表的外键约束--
----------------------------------------------------
DECLARE Employee_Cursor CURSOR FOR
select name from sysobjects where xtype='u' and (not name LIKE 'dtproperties') order by [name] asc;
declare @tablename varchar(300);
declare @str varchar(1000);
declare @rst int;
--SET @MyVariable = MyCursor
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
select @str = 'ALTER TABLE '+ @tablename + ' CHECK CONSTRAINT ALL';
EXECUTE(@str);
FETCH NEXT FROM Employee_Cursor into @tablename;
END
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
/****************************************************/
----------------------清空数据库数据的SQL代码--------------
/**********************************************************************/
------------------------------------------------------
use TSZongHe_new --选择你要清空的数据库
-------------------------------------------------------
-------------禁用本库中所有表的外键约束-----
-------------------------------------------------------
DECLARE Employee_Cursor CURSOR FOR
select name from sysobjects where xtype='u' and (not name LIKE 'dtproperties') order by [name] asc;
declare @tablename varchar(300);
declare @str varchar(1000);
declare @rst int;
--SET @MyVariable = MyCursor
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
select @str = 'ALTER TABLE '+ @tablename + ' NOCHECK CONSTRAINT ALL';
EXECUTE(@str);
FETCH NEXT FROM Employee_Cursor into @tablename;
END
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
----------------------------------------------------
--------------删除本库中所有表数据----------
----------------------------------------------------
DECLARE Employee_Cursor CURSOR FOR
select name from sysobjects where xtype='u' and (not name LIKE 'dtproperties')
-------------------------------------------------------------------------------------------
--如果你想清空库中所有表的话,就不需要下段这部分代码
-------------------------------------------------------------------------------------------
and (name not in ('PageRights','ModuleInfo','RoleModuleRight','RoleInfo','UserInfo',
'PersonnelInfo','SystemDirectory','MarriageState','TechnicalPostInfo','EducationInfo',
'PlaceInfo ','DepartmentInfo ','FoodStandard','FieldsValue','PublicResShare','StyleInfo',
'DocumentTypeInfo','AddressKindInfo','PayTypeInfo','FoodStandard','CityInfo','HotelType',
'FootQuomodoInfo','HotelGradeInfo','PlaceArea','TravelAgencySortInfo','BusTypeInfo'))
--------------------------------------------------------------------------------------------
--如果你想保留某些表数据的话,可以加上下面这段代码,当然表名根据自己的情况写
--------------------------------------------------------------------------------------------
order by [name] asc;
declare @tablename varchar(300);
declare @str varchar(1000);
declare @rst int;
--SET @MyVariable = MyCursor
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
select @str = 'Delete '+ @tablename ;
EXECUTE(@str);
FETCH NEXT FROM Employee_Cursor into @tablename;
END
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
----------------------------------------------------
-------------恢复本库中所有表的外键约束--
----------------------------------------------------
DECLARE Employee_Cursor CURSOR FOR
select name from sysobjects where xtype='u' and (not name LIKE 'dtproperties') order by [name] asc;
declare @tablename varchar(300);
declare @str varchar(1000);
declare @rst int;
--SET @MyVariable = MyCursor
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @tablename;
WHILE @@FETCH_STATUS = 0
BEGIN
select @str = 'ALTER TABLE '+ @tablename + ' CHECK CONSTRAINT ALL';
EXECUTE(@str);
FETCH NEXT FROM Employee_Cursor into @tablename;
END
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;
GO
/****************************************************/
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 智能桌面机器人:用.NET IoT库控制舵机并多方法播放表情
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 新年开篇:在本地部署DeepSeek大模型实现联网增强的AI应用
· DeepSeek火爆全网,官网宕机?本地部署一个随便玩「LLM探索」
· Janus Pro:DeepSeek 开源革新,多模态 AI 的未来
· 上周热点回顾(1.20-1.26)
· 【译】.NET 升级助手现在支持升级到集中式包管理