Truncate selected tables in SQL Server

Question

How to truncate selective tables in SQL Server 2008, I have a list of tables which may be excluded during truncate process.

Can anybody guide me?

Answer 1

You can simply run TRUNCATE TABLE yourTableName. If you have a list you can run the following:

CREATE TABLE #truncateTables(name nvarchar(255))

INSERT INTO #truncateTables(name) VALUES(N'dbo.yourTable1'),(N'dbo.yourTable2')

DECLARE cur CURSOR FOR
SELECT name FROM #truncateTables

OPEN cur

DECLARE @sql nvarchar(max), @tabname nvarchar(255)

FETCH NEXT FROM cur INTO @tabname

WHILE @@FETCH_STATUS = 0 BEGIN
    SET @sql = N'TRUNCATE TABLE '+@tabname
        BEGIN TRY EXEC(@sql)
    END TRY
    BEGIN CATCH -- if a foreign key constraint exists
        SET @sql = N'DELETE FROM '+@tabname
        EXEC(@sql)
    END CATCH

    FETCH NEXT FROM cur INTO @tabname

END

CLOSE cur
DEALLOCATE cur

DROP TABLE #truncateTables