Our Blog

Delete table rows that are not referenced from any other table

Yavor Valchev
by Yavor Valchev on Tue 17 December 2013 No comments

Recently I had an interesting issue to solve with the database – I had a table “Locations” and I wanted to get rid of all locations that are not used in the database. In my case, I have a user which has a residence location, a company which has a headquarters location, a shop which also has a location. Anyway, I had to delete some of the users, companies, shop and thus I ended up having a database with only the needed users, companies, shops but also with locations “hanging in the air” that are not used by any of these tables. As locations are meant to be used only by one entity, once the entity is deleted its location is worthless so I had to figure out how to get rid of these locations. After googling around in search of a similar case I came to the following resolution of my issue in several steps:

1. Run a query to select the tables and columns in the database that have a reference to the “Locations” table and select a list(table) with two columns (tablename and columnname). In my example I will get something like that:

TableName ColumnName
User ResidenceLocationID
Companies HeadquatersID
Shops LocationID

2. Create a temp table # TempCleanTable to hold the IDs of the locations in use

3. Loop through this list with tables that have reference to the Locations and execute a “select into” from each column in each table to a temporary table, something like:


EXEC('INSERT INTO #TempCleanTable SELECT ResidenceLocationID AS ID FROM Users WHERE ResidenceLocationID IS NOT NULL')

4. Delete all locations that are not in the #TempCleanTable like that:


EXEC ('DELETE Locations WHERE ID NOT IN (SELECT DISTINCT ID FROM #TempCleanTable)')

Well, that’s it. See below a sample parameterized sql script that can do that for you as long as you put down in its begging the name of the table you want to clean. Read the comments to understand how it works.

--************************************************************************************************************

--Replace with the name of the table you want to clean.

--IMPORTANT - this query will only work if the primary key column of the table we are deleting is INT

--************************************************************************************************************

Declare @TableToBeCleaned nvarchar(200)

Set @TableToBeCleaned = 'Locations'

SET XACT_ABORT ON; --Set this to ON to automatically rollback the transaction in case of an error

BEGIN TRANSACTION CleanTableTransaction

--We need these two variables to loop the related tables and their columns

DECLARE @RelatedTableName nvarchar(200), @ForeignKeyColumnName nvarchar(200)

DECLARE clean_cursor CURSOR FOR

SELECT * FROM

--************************************************************************************************************

--Load a list of tables and columns that have relations to the primary key column of the table we are cleaning

--************************************************************************************************************

(select t.name as TableWithForeignKey, c.name as ForeignKeyColumn

from sys.foreign_key_columns as fk

inner join sys.tables as t on fk.parent_object_id = t.object_id

inner join sys.columns as c on fk.parent_object_id = c.object_id and fk.parent_column_id = c.column_id

where fk.referenced_object_id = (select object_id from sys.tables where name = @TableToBeCleaned))

as TablesAndColumns

OPEN clean_cursor

FETCH NEXT FROM clean_cursor

INTO @RelatedTableName, @ForeignKeyColumnName

--We will use this temp table to insert all IDs of the table we are cleaning

Create table #TempCleanTable ( ID int )

WHILE @@FETCH_STATUS = 0

--************************************************************************************************************

--Loop through all related tables and select the contents of their related column into the

--#TempCleanTable table

--************************************************************************************************************

BEGIN

exec ('insert into #TempCleanTable select ' + @ForeignKeyColumnName + ' as ID from ' + @RelatedTableName + ' where ' + @ForeignKeyColumnName + ' is not null')

FETCH NEXT FROM clean_cursor

INTO @RelatedTableName, @ForeignKeyColumnName

END

CLOSE clean_cursor;

DEALLOCATE clean_cursor;

--************************************************************************************************************

--Uncomment this line to select all records from the table that you want to clean that are not

--referenced from any table from the database

--EXEC ('SELECT * FROM ' + @TableToBeCleaned + ' WHERE ID NOT IN (SELECT DISTINCT ID FROM #TempCleanTable)')

--Uncomment this line to delete all not-used record from the table to be cleaned

--EXEC ('DELETE ' + @TableToBeCleaned + ' WHERE ID NOT IN (SELECT DISTINCT ID FROM #TempCleanTable)')

--************************************************************************************************************

--Get rid of the temporary table

DROP TABLE #TempCleanTable

COMMIT TRANSACTION CleanTableTransaction

Yavor ValchevDelete table rows that are not referenced from any other table

Related Posts

Take a look at these posts

Join the conversation