I recently needed to delete all the Arena images from the util_blob table in a copy of our production database. Doing a DELETE would take too long since it’s a logged transaction and we have Gigs of stored image data. Using TRUNCATE is what I wanted to do since it’s not a logged transaction and would quickly delete all the rows. Problem is that you can’t use TRUNCATE if the table has any foreign key relationships and the util_blob table has several.
Since this is a common task for me, I wanted to create a script that would drop all the relationships, TRUNCATE the table, and then rebuild the relationships. Enter the SQL System Views which will tell you anything you’d like to know about the structure of your database.
The script below uses these System Views to determine all of the table’s that have a foreign key relationship with util_blob. It then set’s the related field on those tables to null, drops the relationship, does the TRUNCATE, and then rebuilds the relationships, and it will continue to work even when additional relationships are added.
After running this script and shrinking the database it went from over 24 GB to just under 2 GB which is a lot nicer to work with on my small development machine. As a bonus I also got to learn the importance of making sure I know the difference between production and a copy of production when staff started asking what happened to all the images in Arena.
Here’s the script. Make sure not to run it on your production database…
BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ConstraintName varchar(128) DECLARE @IsDisabled bit DECLARE @DeleteAction varchar(128) DECLARE @TableName varchar(128) DECLARE @ColumnName varchar(128) DECLARE @SqlStatement varchar(4000) -- Create a temporary table for storing the SQL statements to recreate the relationships CREATE TABLE #ConstraintTable ([create_contraint] varchar(4000)) -- Create a cursor to spin through all the relationships with the blob_id field on the util_blob table DECLARE RelationshipCursor INSENSITIVE CURSOR FOR SELECT k.name AS constraint_name, k.is_disabled, CASE k.delete_referential_action WHEN 2 THEN 'SET NULL' ELSE 'NO ACTION' END, tso.name, tac.name FROM sys.foreign_key_columns kc INNER JOIN sys.foreign_keys k ON kc.constraint_object_id = k.object_id INNER JOIN sys.all_objects so ON so.object_id = kc.referenced_object_id INNER JOIN sys.all_columns rac ON rac.column_id = kc.referenced_column_id AND rac.object_id = so.object_id INNER JOIN sys.all_objects tso ON tso.object_id = kc.parent_object_id INNER JOIN sys.all_columns tac ON tac.column_id = kc.parent_column_id AND tac.object_id = tso.object_id WHERE so.name = 'util_blob' AND rac.name = 'blob_id' OPEN RelationshipCursor FETCH NEXT FROM RelationshipCursor INTO @ConstraintName, @IsDisabled, @DeleteAction, @TableName, @ColumnName WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS = 0) BEGIN -- Update the foreign table's field value to NULL SET @SqlStatement = 'UPDATE ' + @TableName + ' SET ' + @ColumnName + '= null' EXEC (@SqlStatement) -- Drop the relationship SET @SqlStatement = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName EXEC (@SqlStatement) -- Create the SQL statement to recreate the relationship and save it to the temporary table INSERT INTO #ConstraintTable VALUES ( 'ALTER TABLE ' + @TableName + ' ADD ' + CHAR(13) + CHAR(10) + ' CONSTRAINT ' + @ConstraintName + ' FOREIGN KEY(' + @ColumnName + ') REFERENCES util_blob(blob_id) ON DELETE ' + @DeleteAction + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) ) IF @IsDisabled = 1 BEGIN INSERT INTO #ConstraintTable VALUES ( 'ALTER TABLE ' + @TableName + CHAR(13) + CHAR(10) + ' NOCHECK CONSTRAINT ' + @ConstraintName + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) ) END END FETCH NEXT FROM RelationshipCursor INTO @ConstraintName, @IsDisabled, @DeleteAction, @TableName, @ColumnName END CLOSE RelationshipCursor DEALLOCATE RelationshipCursor -- Truncate the table TRUNCATE TABLE util_blob -- Create a cursor to spin through all the create statements DECLARE ConstraintCursor INSENSITIVE CURSOR FOR SELECT create_contraint FROM #ConstraintTable OPEN ConstraintCursor FETCH NEXT FROM ConstraintCursor INTO @SqlStatement WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS = 0) BEGIN -- Execute the SQL statement to recreate the relationship. EXEC (@SqlStatement) END FETCH NEXT FROM ConstraintCursor INTO @SqlStatement END CLOSE ConstraintCursor DEALLOCATE ConstraintCursor DROP TABLE #ConstraintTable -- Update the statistics on the table since all the records have been deleted UPDATE STATISTICS util_blob SET NOCOUNT OFF COMMIT TRANSACTION
Nice script and… LOL/ouch on the ‘copy of’ issue. I’ve felt that kind of pain before too.