After deleting a category from my store,  I ran:

select * FROM Smith_CategoryProduct where not exists (select *
FROM Smith_Category where Smith_Category.CategoryID =
Smith_CategoryProduct.CategoryID
)

which returned all the products associated with the deleted category, eventually Smith_CategoryProduct will become huge.