Truncate All Table Data in MSSQL

Truncate All Table Data

Truncating all tables in a database can be done using dynamic SQL. Be cautious when executing this kind of operation, as it will delete all data in all tables without the option to rollback.

Here's an example of how you might construct a query to truncate all tables in a SQL Server database.

DECLARE @TableName NVARCHAR(128)
DECLARE @TruncateQuery NVARCHAR(MAX)

DECLARE tableCursor CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN tableCursor

FETCH NEXT FROM tableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @TruncateQuery = 'TRUNCATE TABLE ' + @TableName
    EXEC sp_executesql @TruncateQuery

    FETCH NEXT FROM tableCursor INTO @TableName
END

CLOSE tableCursor
DEALLOCATE tableCursor

Please note

• This is a dangerous operation: Truncating tables removes all data without any means of recovery.
• Backup your data: Before executing any query that modifies data in this way, it's essential to have a backup to restore the data if needed.
• Review and test carefully: Always review and test thoroughly in a safe environment before executing such queries in a production environment.

It's also crucial to consider permissions and the impact of truncating tables in a live system before performing such operations.