Dynamic SQL can be used to truncate every table in a database. When performing this kind of operation, use caution because there is no way to undo the deletion of all data across all tables.

This is an illustration of how to write a query in a SQL Server database to truncate every table.

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

Kindly take note of

• This operation carries a risk: When tables are truncated, all data is lost and cannot be recovered.
• Make a backup of your data: It's imperative to have a backup of your data in case the original is needed before running any queries that alter data in this manner.
• Examine and make sure it passes: Before running such queries in a production environment, always properly evaluate and test in a secure setting.

Before carrying out such operations, it's also imperative to take permissions and the effect of truncating tables in a live system into account.

HostForLIFEASP.NET SQL Server 2022 Hosting