Delete an AX company on SQL

February 3, 2010 at 21:03
filed under Dynamics AX
Tagged ,

This week, we were shrinking a database of a development environment by deleting some companies.
Here a nice little SQL statement that uses the sp_MSforeachtable stored procedure to delete all records of a specific company (CEU in this case) from all tables.

EXEC sp_MSforeachtable 'delete from ? where ?.DataAreaID = "CEU"'

Certainly fast(er than AX) and gets the job done.
Use at your own risk ;-)

You’ll want do do some cleaning up to:
delete the company id from the DataArea table and from the CompanyDomainList table

DELETE FROM CompanyDomainList WHERE CompanyDomainList.CompanyID = 'CEU'

Update 2:
When you want to delete all companies except a few (like DAT), just use this:

EXEC sp_MSforeachtable 'delete from ? where ?.DataAreaID <> "DAT" AND ?.DataAreaID <> "DEMO"'
DELETE FROM DataArea WHERE DataArea.ID <> 'DAT' AND DataArea.ID <> 'DEMO'
DELETE FROM CompanyDomainList WHERE CompanyDomainList.CompanyID <> 'DAT' AND CompanyDomainList.CompanyID <> 'DEMO'

This will delete all companies except DAT en DEMO.


RSS / trackback

  1. Alex

    There are many other tables that also have a company identifier in a field other than “DataAreaId”. You have already seen from your 2nd update the table DataArea has one called “Id” and no DataAreaId.

    I’d be worried this misses some data.

  2. Klaas Deforche

    Hi Alex,

    The company for a record is always stored in the DataAreaId field. Other field that contain a dataAreaId are always references to a DataAreaId field.
    When you delete an AX company, you can always end op with data that references a company that doesn’t exist anymore, but that will be data stored in other companies than you are deleting.

    I’m fairly sure all records will be deleted for that company, but again, this is just a ‘quick and dirty’ way to do this. In a production environment, I would certainly use the standard AX way of deleting companies.

  3. Tommy Skaue

    You may also have to clean up the VirtualDataAreaList table (

  4. Henry


    I have the admin privileges on my stand alone computer where i installed the Dynamics AX 2009, i created the three companies with their demo data imported, but the data didn’t imported successfully, so i deleted the three companies , after deleting the CEU company, my hard drive space of 4 GB was consumed ……. can anyone tell me what might be the problem, if there is any way to free that occupied space, Kindly resolve this problem ASAP.

    Thanks in advance,


  5. Henry

    this is how i deleted,

    In AX, i selected the company from the company account list which is at the right bottom and right-click –> go to main table and i deleted it from there.

  6. Klaas Deforche

    Hi Henry,

    I agree, deleting the companies in AX is better (“cleaner”),
    on sql, it’s quick an dirty.

    Your disk space loss is probably due to the fact that your database size has grown, even though you deleted the data.
    There is a way to shrink your database (eg:

  7. Eugene

    “I agree, deleting the companies in AX is better (“cleaner”),
    on sql, it’s quick an dirty.”

    Come on, open your database in SSMS and see if you can find any records referencing company account you deleted in AX.

    I currently run upgrade validation checklist for AX 2012, and there are heaps of tables containing reference to company accounts that are loooooong gone.

    So if you like it nice and clean, do it in AX, but then run a clean up job like in this post.

    Thanks a lot!

  8. Jose Luis Olabarri

    Please, don´t do this to delete organizations. You will have problems with the recid of the new rows inserted in the database.