Summary of the deletion of table contents in the database (always have what you need)

1. Bulk emptying of tables in the database delete (use caution)

Use PL123

EXEC sp_msforeachtable ' ALTER TABLE? NOCHECK CONSTRAINT All '

EXEC sp_msforeachtable ' ALTER TABLE? DISABLE TRIGGER All '

EXEC sp_msforeachtable ' DELETE from? '

EXEC sp_msforeachtable ' ALTER TABLE? CHECK CONSTRAINT All '

EXEC sp_msforeachtable ' ALTER TABLE? ENABLE TRIGGER All '

EXEC sp_msforeachtable ' SELECT * from? '

EXEC sp_msforeachtable ' drop table? '

GO

2. Clear the table

Truncate Table TableName

Delete from TableName

3. Cursor implementation empty the specified table !!! Cursor Classic Applications

Use TOMR_SYS_C_LP --Select the database you want to empty

---Disable foreign KEY constraints for all tables in this library
DECLARE Employee_cursor Cursor for
Select name from sysobjects where xtype= ' u ' and (not the name like ' dtproperties ') Order BY [name] ASC;
DECLARE @tablename varchar (300);
DECLARE @str varchar (1000);
declare @rst int;
OPEN Employee_cursor;
FETCH NEXT from Employee_cursor to @tablename;
While @ @FETCH_STATUS = 0
BEGIN
Select @str = ' ALTER TABLE ' + @tablename + ' NOCHECK CONSTRAINT all ';
EXECUTE (@str);
FETCH NEXT from Employee_cursor to @tablename;
END
CLOSE Employee_cursor;
Deallocate employee_cursor;
GO

-delete data in table
DECLARE delete_cursor Cursor for
Select name from sysobjects where xtype= ' u ' and (not the name like ' dtproperties ') Order BY [name] ASC;
Declare @tablename varchar (300);
Declare @str varchar (1000);
Declare @rst int;
OPEN Delete_cursor;
FETCH NEXT from Delete_cursor to @tablename;
While @ @FETCH_STATUS = 0
BEGIN
--Here you do not need to delete except the table name of the data  
IF (@ TableName = ' xx ')
Begin
Print (' table data not deleted   ' [email protected])
End
ElSE
Begin
Select @str = ' Delete from ' + @tablename;
EXECUTE (@str);
Print (' Delete table data   End
FETCH NEXT from Delete_cursor to @ TableName
END
CLOSE delete_cursor;
Deallocate delete_cursor;
GO

--Restore all table foreign KEY constraints
DECLARE Employee_cursor Cursor for
Select name from sysobjects where xtype= ' u ' and (not the name like ' dtproperties ') Order BY [name] ASC;
DECLARE @tablename varchar (300);
DECLARE @str varchar (1000);
declare @rst int;
OPEN Employee_cursor;
FETCH NEXT from Employee_cursor to @tablename;
While @ @FETCH_STATUS = 0
BEGIN
Select @str = ' ALTER TABLE ' + @tablename + ' CHECK CONSTRAINT all ';
EXECUTE (@str);
FETCH NEXT from Employee_cursor to @tablename;
END
CLOSE Employee_cursor;
Deallocate employee_cursor;
GO

Instance:

Use Tyzh

--Delete table data readerdata

DECLARE delete_cursor Cursor for

Select name from sysobjects where xtype= ' u ' and (not name like ' dtproperties ')

and name like ' locatedata2011% '

or name like ' readerdata2011% '

or name like ' stayinterval2011% '

ORDER by [name] ASC;

DECLARE @tablename varchar (+);

DECLARE @str varchar (+);

declare @rst int;

OPEN delete_cursor;

FETCH NEXT from Delete_cursor to @tablename;

While @ @FETCH_STATUS = 0

BEGIN

--Write the name of the table where you don't need to delete the data

IF(

@tablename = ' locatedata20111221 '

Or

@tablename = ' ReaderData20111221 '

Or

@tablename = ' stayInterval20111221 '

)

Begin

Print('-------------------------')

Print(' table data not deleted '+@tablename)

End

ElSE

Begin

Select @str = ' Delete from ' + @tablename;

EXECUTE(@str);

Print(' Delete table data '+@tablename)

End

FETCH NEXT from Delete_cursor to @tablename;

END

CLOSE delete_cursor;

Deallocate delete_cursor;

GO

System Storage bulk empties data from all tables in SQL Server

2010-12-19 14:03

Emptying data for all tables in SQL Server View sourceprint?1 sp_msforeachtable @command1 = ' Delete from? ' View sourceprint?01 sp_msforeachtable How to use 02 03 1) Description The 04 system stored procedures sp_msforeachtable and sp_msforeachdb are two unlisted stored procedures provided by Microsoft , starting with MS SQL 6.5.   05 is stored in the master database of SQL Server .   06 07 2) Parameter description : @command1 nvarchar (2000),--The first SQL command to run @replacechar nchar (1) = N '? ',--the specified placeholder symbol @command2 nvarchar (a) = null,--The second SQL command to run One @command3 nvarchar (a) = null,--third run SQL instruction @whereand nvarchar (+) = null,--optional condition to select Table @precommand nvarchar (a) = null, the action before the instruction is executed (similar to the action before the trigger of the control ) @postcommand nvarchar = null-the action after the instruction is executed (similar to the trigger action of the control ) 15 16 3) Example 17--Details of each table in the statistical database sp_msforeachtable exec @command1 = "sp_spaceused '?" 19--Get the number of records and the capacity of each table : EXEC sp_msforeachtable @command1 = "print '? '", @command2 = "sp_spaceused '? '", @command3 = "SELECT count (*) from?" 23--Get all the storage space of the database : EXEC sp_msforeachdb @command1 = "print '? '", @command2 = "sp_spaceused" 26-Check all the databases EXEC sp_msforeachdb @command1 = "print '? '", @command2 = "DBCC CHECKDB (?) " 29--Update statistics for all tables in the pubs database that already start with T : EXEC sp_msforeachtable @whereand = "and name like ' t% '", @replacechar = ' * ', @precommand = "print ' Updating Statistics ..... ' Print '", @command1 = "print ' * ' UPDATE STATISTICS *", @postcommand = "Print" print ' Complete Update statistics! ' " 35--delete data from all tables in the current database Sp_msforeachtable @command1 = ' Delete from? ' PNS sp_msforeachtable @command1 = "TRUNCATE TABLE?" 38 39 4) use of parameter @whereand The @whereand parameter plays the role of instruction condition restriction in the stored procedure , the specific wording is as follows : In a @whereend, you can write @whereand = ' and O.name in (' Table1 ', ' Table2 ',.......) ' 42 Example : I want to update the value of note column null in Table1/table2/table3 Sp_msforeachtable @command1 = ' Update? Set note= ' where NOTE is NULL ', @whereand = ' and O.name in (' Table1 ', ' Table2 ', ' Table3 ') ' 44 45 5) "?" The special use of stored procedures has created these two powerful stored procedures 46 here "?" is equivalent to the role of a wildcard in DOS commands and when we search for files under Windows. print ' Prints all tables that need to be emptied : ' exec sp_msforeachtable @command1 = "print '? '", @whereand = ' and (O.[name] like "yds_%" or o.[name] like ' fks_% ') ' print '---------------------------------------------------------------------' print ' Print table with no foreign KEY constraint : ' exec sp_msforeachtable @command1 = " print '? ', @whereand = ' and ObjectProperty (o.id, ' tablehasforeignref ') =0 and xtype= ' U ' and (O.[name] like ' yds_% ' or o.[name] like ' sgcb_% ' or o.[name] like ' ydzy_% ' or o.[name] ' --empty table with no foreign KEY constraint document_% exec sp_msforeachtable @command1 =" truncate table? ", @whereand = ' and ObjectProperty (O.id, ' Tablehasforeignref ') =0 and xtype= ' U ' and (O.[name] like "yds_%" or o.[name] like "sgcb_%" or o.[name] like "ydzy_% ' or o.[name] like ' document_% ') ' print '-------------------------------------------------------------------- -' TRUNCATE TABLE chzl_xx TRUNCATE TABLE BookMarks print ' prints a table with foreign KEY constraints : ' exec sp_msforeachtable @command1 = "print '? '", @whereand = ' and ObjectProperty (o.id, ' tablehasforeignref ') =1 and xtype= ' U ' and (O.[name] like ' yds_% ' or O.[name] Li Ke ' sgcb_% ' or o.[name] like "ydzy_%" or o.[name] like ' document_% ') ' --Empty tables with foreign KEY constraints exec sp_msforeachtable @command1 = "Delete from?", @whereand = ' and ObjectProperty (o.id, ' tablehasforeignref ') =1 and xtype= ' U ' and (O.[name] like ' yds_% ' or O.[name] Li Ke ' sgcb_% ' or o.[name] like "ydzy_%" or o.[name] like ' document_% ') ' print '---------------------------------------------------------------------' print ' Reset maxids table : ' --Reset maxids table Update Maxids Set maxid=1

Summary of the deletion of table contents in the database (always have what you need)