Dec
7

SQL System Views

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
Posted by David Turner Arena Subscribe to RSS feed

1 Comment on SQL System Views

  1. Nick Airdo says:
    December 11, 2007 at 10:18 pm

    Nice script and… LOL/ouch on the ‘copy of’ issue. I’ve felt that kind of pain before too. :)

Leave a Reply

*