/*************************************** *******************************/
-------------------- SQL code for clearing database data --------------
/*************************************** *******************************/
------------------------------------------------------
Use TSZongHe_new -- select the database you want to clear
-------------------------------------------------------
------------- Disable foreign key constraints for all tables in this database -----
-------------------------------------------------------
DECLARE Employee_Cursor CURSOR
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
----------------------------------------------------
-------------- Delete all table data in this database ----------
----------------------------------------------------
DECLARE Employee_Cursor CURSOR
Select name from sysobjects where xtype = 'U' and (not name LIKE 'dtproperties ')
Bytes -------------------------------------------------------------------------------------------
-- If you want to clear all tables in the database, you do not need the following code:
Bytes -------------------------------------------------------------------------------------------
And (name not in ('pagerights ', 'leleinfo', 'rolemoduleright', 'roleinfo', 'userinfo ',
'Personnelinfo', 'systemdirectory', 'marriagestate', 'technicalpostinfo', 'educationinfo ',
'Placeinfo', 'destentinfo', 'foodstandard', 'fieldsvalue', 'publicresshare', 'styleinfo ',
'Documenttypeinfo', 'sssskindinfo', 'paytypeinfo', 'foodstandard ', 'cityinfo', 'struct type ',
'Footquomodoinfo', 'gradeinfo', 'placearea ', 'travelagencysortinfo', 'bustypeinfo '))
Bytes --------------------------------------------------------------------------------------------
-- If you want to retain some table data, you can add the following code. Of course, the table name is based on your own situation.
Bytes --------------------------------------------------------------------------------------------
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
----------------------------------------------------
------------- Restore the foreign key constraints of all tables in the database --
----------------------------------------------------
DECLARE Employee_Cursor CURSOR
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