Delete All Tables By Dependency

Posted: October 29, 2008 in SQL Server 2005

To delete all table records from a database by dependency, open a new query window and execute the following line of code:

-- Declare variables
DECLARE @DB AS VARCHAR(10);

-- Set database name
SET @DB = '';

-- Check if the temp table already exists and drop it if it does
IF OBJECT_ID ('tempdb..#tmpTableList') IS NOT NULL
    DROP TABLE tempdb.#tmpTableList;

-- Create a temp table for the table list
CREATE TABLE #tmpTableList
            (oType     INT
            ,oName     VARCHAR(100)
            ,oOwner    VARCHAR(100)
            ,oSequence INT
            );

-- Execute system procedure to get the table dependencies
EXEC('Use ' + @DB + ' INSERT #tmpTableList EXEC sp_MSdependencies NULL, 3, NULL,
NULL, 1');

-- Declare cursor to get all the records in the temp table
DECLARE  curTableList CURSOR READ_ONLY FOR
SELECT   oName
FROM     #tmpTableList
WHERE    oName NOT IN ('')
ORDER BY oSequence DESC;

-- Declare variables
DECLARE @oName VARCHAR(100);

-- Open cursor
OPEN curTableList;
FETCH NEXT FROM curTableList INTO @oName;

-- Loop through all the records in the cursor
WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @oName = @DB + '.dbo.' + @oName;
        -- Display table that will be deleted
        PRINT CHAR(13) + 'DELETE FROM ' + @oName;
        -- Delete the table
        EXEC ('DELETE FROM ' + @oName);
        -- Get the next record from the cursor
        FETCH NEXT FROM curTableList INTO @oName;
    END;

-- Clean up the cursor
CLOSE      curTableList;
DEALLOCATE curTableList;
Advertisements
Comments
  1. Kelvin M says:

    This code is brilliant, it works well, thanks heaps

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s