How to TRUNCATE all tables in Microsoft SQL SERVER instance

When we are developing some software and we want to clean the SQL database to simulate a new installation, it would be interesting to have a routine that would erase the contents of all tables.

This script performs this task, it deletes the contents of all the tables of the instance that is open in SQL Server Management Studio.

 ATTENTION: The TRUNCATE command erases all the contents of the tables and there is no way to restore, only with backup. Use this script very consciously and in test environments where you really want to do a general clean up in your database. I am not responsible for the use of this in your bank. 

Created by my friend Airton Junior , it follows the killer script... 


USE MASTER;
--TABELA TEMPORÁRIA PARA ARMAZENAR OS BANCOS SEPARADOS PARA LIMPEZA
CREATE TABLE #TEMPORARIA (NOMEBANCO VARCHAR(255));
GO

--SEPARATION OF DATABASES
INSERT INTO #TEMPORARIA (NOMEBANCO)
SELECT name
FROM master.sys.databases
WHERE name LIKE '%BANKNAME%'
AND name NOT LIKE '%TABLES THAT WILL BE KEPT%'

DECLARE @NOMEDB VARCHAR(255)
DECLARE @SQL AS VARCHAR(MAX)

--SCREEN ALL BANKS CLEARING THEIR TABLES
WHILE (
SELECT Count(*)
FROM #TEMPORARIA
) > 0

BEGIN

--GETTING THE BANK IN POSITION ONE OF THE LIST
SELECT TOP 1 @NOMEDB = NOMEBANCO
FROM #TEMPORARIA

SET @SQL = N'USE ' + QUOTENAME(@NOMEDB);

EXECUTE (@SQL + 'EXEC sp_MSForEachTable ''TRUNCATE TABLE ?''');

--DELETING THE BANK USED IN CLEANING THE LISTING DELETE FROM
DELETE
FROM #TEMPORARIA
WHERE NOMEBANCO = @NOMEDB
END

--APAGANDO A TABELA TEMPORÁRIA
DROP TABLE #TEMPORARIA