Clears data from all tables in a database.

To clear data from all tables in a database, run the following command: execsp_MSforeachtableTRUNCATETABLE? **************************************** * ******************************* About sp_MSforeachtable and sp_MSforeachdb1) description: sp_MSforeachta

To clear data from all tables in a database, run the following command: exec sp_MSforeachtable truncate table? **************************************** * ******************************* About sp_MSforeachtable and sp_MSforeachdb 1) description: sp_MSforeachta

To clear data from all tables in a database, run the following command:

Exec sp_MSforeachtable "truncate table? "

**************************************** ********************************

About sp_MSforeachtable and sp_MSforeachdb

1) Description
System stored procedures sp_MSforeachtable and sp_MSforeachdb are two undisclosed stored procedures provided by Microsoft, starting from ms SQL 6.5.
Stored in the MASTER database of SQL Server.

2) parameter description:
@ Command1 nvarchar (2000), -- the first running SQL command
@ Replacechar nchar (1) = n '? ', -- The specified placeholder.
@ Command2 nvarchar (2000) = null, -- the second running SQL command
@ Command3 nvarchar (2000) = null, -- the third running SQL command
@ Whereand nvarchar (2000) = null, -- an optional condition to select a table
@ Precommand nvarchar (2000) = null, -- the operation before the command is executed (similar to the operation before the control is triggered)
@ Postcommand nvarchar (2000) = null -- the operation after the command is executed (similar to the operation after the control is triggered)

3) Example
-- Details of each table in the Statistics Database
Exec sp_MSforeachtable @ command1 = "sp_spaceused '? '"
-- Obtain the number and capacity of records for each table:
EXEC sp_MSforeachtable @ command1 = "print '? '",
@ Command2 = "sp_spaceused '? '",
@ Command3 = "SELECT count (*) FROM? "
-- Obtain the storage space of all databases:
EXEC sp_MSforeachdb @ command1 = "print '? '",
@ Command2 = "sp_spaceused"
-- Check all databases
EXEC sp_MSforeachdb @ command1 = "print '? '",
@ Command2 = "dbcc checkdb (?) "
-- Update statistics of all tables starting with "t" in the PUBS database:
EXEC sp_MSforeachtable @ whereand = "and name like't % '",
@ Replacechar = '*',
@ Precommand = "print 'updating Statistics... 'print ''",
@ Command1 = "print '*' update statistics *",
@ Postcommand = "print ''print 'complete Update Statistics! '"
-- Delete data from all tables of the current database
Sp_MSforeachtable @ command1 = 'delete from? '
Exec sp_MSforeachtable "truncate table? "

4) parameter @ whereand usage
@ Whereand the parameter plays a role as a command condition restriction in the stored procedure. The specific statement is as follows:
@ Whereend, you can write @ whereand = 'AND o. name in (''table1', ''table2 '',.......)'
For example, I want to update the NULL value of the NOTE column in Table1/Table2/Table3.
Sp_MSforeachtable @ command1 = 'Update? Set NOTE = ''' Where NOTE is null', @ whereand = 'AND o. name in (''table1', ''table2', ''table3 '')'

5 )"? "The special usage of stored procedures has created these two powerful stored procedures
Here "? "Is equivalent to the wildcard in the doscommand and when we search for files in WINDOWS.