Sfrgttk

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

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.

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.