Delete Company in AX 2009 using SQL

One of the potential tasks when upgrading to a new version of Dynamics AX (like from AX2009 to AX2012) is getting rid of obsolete companies. Microsoft shared a SQL for this a few years back. I enhanced it a little bit and added some additional statements.

Just one important remark - DO NOT RUN THIS AGAINST AX2012!

In the interest of sharing, here it is:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

/********************************************************  REMOVE COMPANYID IN DYNAMICS AX 2009    USE AT OWN RISK!    MAKE SURE YOUR TRANSACTION LOG IS PERMITTED TO GROW     Inspired by:  http://blogs.msdn.com/b/emeadaxsupport/archive/2010/12/09/how-to-delete-orphaned-data-remained-from-deleted-company.aspx    Tommy Skaue http://yetanotherdynamicsaxblog.blogspot.com/ *********************************************************/   DECLARE @_TABLENAME NVARCHAR(40) DECLARE @_COMPANYID NVARCHAR(4)   SET @_COMPANYID = N'TST';  -- COMPANY TO DELETE   DECLARE CURSQLDICTIONARY CURSOR FOR SELECT A.SQLNAME  FROM SQLDICTIONARY A   INNER JOIN SQLDICTIONARY X ON X.TABLEID = A.TABLEID AND X.FIELDID = 61448    WHERE A.FIELDID = 0     AND A.FLAGS = 0   OPEN CURSQLDICTIONARY   FETCH NEXT FROM CURSQLDICTIONARY INTO @_TABLENAME   WHILE @@FETCH_STATUS = 0 BEGIN  DECLARE @_SQL NVARCHAR(4000)  SET @_SQL = N'DELETE FROM ' + QUOTENAME(@_TABLENAME) + N' WHERE DATAAREAID = @_DATAAREAID'  PRINT (CHAR(13) + 'Removing ' + @_COMPANYID + ' from ' + @_TABLENAME + '...')  EXEC SP_EXECUTESQL @_SQL, N'@_DATAAREAID NVARCHAR(4)', @_DATAAREAID = @_COMPANYID      FETCH NEXT FROM CURSQLDICTIONARY INTO @_TABLENAME END   PRINT (CHAR(13) + 'Finalizing...') DELETE FROM DATAAREA WHERE DATAAREA.ID = @_COMPANYID DELETE FROM COMPANYDOMAINLIST WHERE COMPANYDOMAINLIST.COMPANYID = @_COMPANYID DELETE FROM VIRTUALDATAAREALIST WHERE VIRTUALDATAAREALIST.ID = @_COMPANYID PRINT (CHAR(13) + 'Done!') CLOSE CURSQLDICTIONARY DEALLOCATE CURSQLDICTIONARY

Use at own risk (of course), and let me know if you find any issues with it.