delete from all tables

[Alfredomulet]

hello everyone, is it there a SQL query that will empty all the tables in my database? Something like DELETE FROM ALL_TABLES...

Thanks!
alfredo

 

[SQLBill]

Check out FAQ183-4107. The undocumented stored procedure sp_MSforeachtable might be what you want.

-SQLBill

 

[Alfredomulet]

Thanks SQLBill, your tip really helped!
I've created the following procedure to clean up my tables:

IF (OBJECT_ID ('dbo.DeleteRecordsAllTables') IS NOT NULL)
	DROP PROCEDURE dbo.DeleteRecordsAllTables
GO
CREATE PROCEDURE dbo.DeleteRecordsAllTables AS
BEGIN
	SET NOCOUNT ON

	-- Declare a cursor that gets a list of all the
        -- tables in the database
	
	DECLARE tablesList CURSOR FOR
		SELECT table_name FROM information_schema.tables
			WHERE table_type = 'BASE TABLE'
			ORDER BY table_name

	-- Scratch variables used in the program
	DECLARE @lcTableName VARCHAR(100)
	DECLARE @sqlString NVARCHAR(1000)

	-- Output table that contains all the results
	CREATE TABLE #tableStodelete (tableName VARCHAR(100))
	
	-- Open the cursor and loop through it
	OPEN tablesList
	FETCH NEXT FROM tablesList INTO @lcTableName
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		-- From the command to execute against each table and execute
		-- the same
		SET @sqlString = 'DELETE FROM ' + @lcTableName
		EXEC sp_executesql @sqlString

		
	END
	-- Cleanup the cursor
	CLOSE tablesList
	DEALLOCATE tablesList

END
GO

EXEC dbo.DeleteRecordsAllTables

And it seems to be working just fine. However it takes a long time to clean up the 3 tables I created in a sample database. Any suggestions to make it faster?
cheers,
alfredo

 

[mrdenny]

Alfredo,
Use the sp_MSForEachTable command.

exec sp_MSForEachTable 'truncate table ?'

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[Alfredomulet]

you're right... ... thanks!!

EXEC sp_MSforeachtable @command1 = 'DELETE FROM ?'