T-SQL语句实现清空数据库数据的代码,保留原有表形式和各种约束

 1)禁用本库中所有表的外键约束

DECLARE Employee_Cursor CURSOR FOR
select name from sysobjects where xtype='u' and (not name LIKE '数据库名') 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;

2）恢复本库中所有表的外键约束

DECLARE Employee_Cursor CURSOR FOR
select name from sysobjects where xtype='u' and (not name LIKE '数据库名') 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;

 

    3) 删除本库中所有表数据

        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;