-1

Is there a way to truncate all tables in a database without using sp_MSForEachTable in TSQL Language? Background: not my call, DBA company policy does not allow sp_MSForEachTable.

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

Wondering if this is most efficient way, or another option available?

select 'truncate table ' + Table_Schema + '.' + Table_Name from INFORMATION_SCHEMA.tables where table_type = 'base table'

Another site recommends this: https://www.mssqltips.com/sqlservertip/3218/truncate-all-tables-in-a-sql-server-database/

 /* TRUNCATE ALL TABLES IN A DATABASE */
DECLARE @dropAndCreateConstraintsTable TABLE
        (
         DropStmt VARCHAR(MAX)
        ,CreateStmt VARCHAR(MAX)
        )
/* Gather information to drop and then recreate the current foreign key constraints  */
INSERT  @dropAndCreateConstraintsTable
        SELECT  DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
                + '].[' + ForeignKeys.ForeignTableName + '] DROP CONSTRAINT ['
                + ForeignKeys.ForeignKeyName + ']; '
               ,CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
                + '].[' + ForeignKeys.ForeignTableName
                + '] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName
                + '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn
                + ']) REFERENCES [' + SCHEMA_NAME(sys.objects.schema_id)
                + '].[' + sys.objects.[name] + ']([' + sys.columns.[name]
                + ']); '
        FROM    sys.objects
        INNER JOIN sys.columns
                ON ( sys.columns.[object_id] = sys.objects.[object_id] )
        INNER JOIN ( SELECT sys.foreign_keys.[name] AS ForeignKeyName
                           ,SCHEMA_NAME(sys.objects.schema_id) AS ForeignTableSchema
                           ,sys.objects.[name] AS ForeignTableName
                           ,sys.columns.[name] AS ForeignTableColumn
                           ,sys.foreign_keys.referenced_object_id AS referenced_object_id
                           ,sys.foreign_key_columns.referenced_column_id AS referenced_column_id
                     FROM   sys.foreign_keys
                     INNER JOIN sys.foreign_key_columns
                            ON ( sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.[object_id] )
                     INNER JOIN sys.objects
                            ON ( sys.objects.[object_id] = sys.foreign_keys.parent_object_id )
                     INNER JOIN sys.columns
                            ON ( sys.columns.[object_id] = sys.objects.[object_id] )
                               AND ( sys.columns.column_id = sys.foreign_key_columns.parent_column_id )
                   ) ForeignKeys
                ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] )
                   AND ( ForeignKeys.referenced_column_id = sys.columns.column_id )
        WHERE   ( sys.objects.[type] = 'U' )
                AND ( sys.objects.[name] NOT IN ( 'sysdiagrams' ) )
/* SELECT * FROM @dropAndCreateConstraintsTable AS DACCT  --Test statement*/
DECLARE @DropStatement NVARCHAR(MAX)
DECLARE @RecreateStatement NVARCHAR(MAX)
/* Drop Constraints */
DECLARE Cur1 CURSOR READ_ONLY
FOR
        SELECT  DropStmt
        FROM    @dropAndCreateConstraintsTable
OPEN Cur1
FETCH NEXT FROM Cur1 INTO @DropStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @DropStatement
            EXECUTE sp_executesql @DropStatement
            FETCH NEXT FROM Cur1 INTO @DropStatement
      END
CLOSE Cur1
DEALLOCATE Cur1
/* Truncate all tables in the database in the dbo schema */
DECLARE @DeleteTableStatement NVARCHAR(MAX)
DECLARE Cur2 CURSOR READ_ONLY
FOR
        SELECT  'TRUNCATE TABLE [dbo].[' + TABLE_NAME + ']'
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_SCHEMA = 'dbo'
                AND TABLE_TYPE = 'BASE TABLE'
  /* Change your schema appropriately if you don't want to use dbo */
OPEN Cur2
FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @DeleteTableStatement
            EXECUTE sp_executesql @DeleteTableStatement
            FETCH NEXT FROM Cur2 INTO @DeleteTableStatement
      END
CLOSE Cur2
DEALLOCATE Cur2
/* Recreate foreign key constraints  */
DECLARE Cur3 CURSOR READ_ONLY
FOR
        SELECT  CreateStmt
        FROM    @dropAndCreateConstraintsTable
OPEN Cur3
FETCH NEXT FROM Cur3 INTO @RecreateStatement
WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @RecreateStatement
            EXECUTE sp_executesql @RecreateStatement
            FETCH NEXT FROM Cur3 INTO @RecreateStatement
      END
CLOSE Cur3
DEALLOCATE Cur3
GO   

This answer uses, so cannot utilize: sp_MSForEachTable

https://stackoverflow.com/questions/155246/how-do-you-truncate-all-tables-in-a-database-using-tsql

0

I'm not sure why you would want to truncate all tables in a database, but, there are several ways to do this. You can use a cursor to loop through sys.tables or INFORMATION_SCHEMA.TABLES:

DECLARE @SqlCmd NVARCHAR(MAX)

DECLARE C1 CURSOR FOR
    SELECT N'TRUNCATE TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
    FROM sys.tables t
    INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
    WHERE t.[name] <> 'sysdiagrams' 
        AND t.is_ms_shipped = 0

OPEN C1

FETCH NEXT FROM C1 INTO @SqlCmd

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @SqlCmd
    -- EXEC sp_executesql @SqlCmd

    FETCH NEXT FROM C1 INTO @SqlCmd
END

CLOSE C1
DEALLOCATE C1

Or you could use the COALESCE method to produce a combined string:

DECLARE @SqlCmd NVARCHAR(MAX)

SELECT @SqlCmd = COALESCE(@SqlCmd + CHAR(10), '') + N'TRUNCATE TABLE ' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name) + ';'
FROM sys.tables t
INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE t.[name] <> 'sysdiagrams' 
    AND t.is_ms_shipped = 0

PRINT @SqlCmd

In these examples, you can either copy the printed statements and run them or use sp_executesql to execute the commands directly.

As highlighted by George Palacios, this solution does not handle Foreign Keys. There is a solution for generating commands to drop and recreate foreign keys here which would work in well.

1

This is a slight frame challenge, but it has been mentioned twice in the comments, and seems likely to be the most reasonable approach to solving this problem.

Instead of truncating all of the tables, script out the database schema.

  • Right click the database
  • Tasks
  • Generate Scripts
  • Choose "Script entire database and all database objects"
    • if you prefer, you can also select specific objects if you want to skip database users (for instance)
  • Continue through the wizard to save the script to a file
    • Review the "advanced" options as some items, like triggers, are not scripted by default

Then you can drop the database, and recreate it from that script with 0 rows in all the tables. This has the advantage of not generating a lot of log file activity, and not having to deal with foreign keys and other dependencies.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy