Blog ala Vidar

You ever struggled with deleting everything from a module (test data for instance) before you start the data-transfer of your live data? Here’s a quick script that will loop through all tables in a namespace and delete everything in all tables in that namespace. It will stop after failing 10 times on the same table.

DECLARE @TableName AS NVARCHAR(128)
DECLARE @SQL AS NVARCHAR(MAX)

SELECT
  name AS TableName, 0 AS FailedToDeleteNumber
    INTO #tables
    FROM sys.tables
    WHERE name LIKE 'atbl_Module_%'

WHILE EXISTS (SELECT * FROM #tables WHERE FailedToDeleteNumber < 10)
BEGIN
    SELECT TOP 1
      @TableName = TableName
        FROM #tables
        ORDER BY FailedToDeleteNumber, TableName

    SET @SQL = 'DISABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)

    BEGIN TRY
        SET @SQL = 'DELETE FROM [' + @TableName + ']'
        EXEC (@SQL)

        DELETE
            FROM #tables
            WHERE TableName = @TableName
    END TRY
    BEGIN CATCH
        UPDATE #tables
            SET FailedToDeleteNumber = FailedToDeleteNumber + 1
            WHERE TableName = @TableName
    END CATCH

    SET @SQL = 'ENABLE TRIGGER ALL ON [' + @TableName + ']'
    EXEC (@SQL)
END

DROP TABLE #tables