How to delete orphaned data remained from deleted company?

  • Article

In AX 2009 it may happen that you delete a company but data related to this company remain in the database. You can use this script to delete this data:

 /*
 * Code Sample Disclaimer:
 * Microsoft provides programming examples for illustration only, 
 * without warranty either expressed or implied, including, but not limited to, 
 * the implied warranties of merchantability or fitness for a particular purpose. 
 * This mail message assumes that you are familiar with the programming  
 * language that is being demonstrated and the tools that are used to create and debug procedures.
 */

DECLARE @_tableName nvarchar(40)
DECLARE @_companyId nvarchar(4)

SET @_companyId = N'<company_id>';  -- replace <company_id> with required company 

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'
    
    EXEC sp_executesql @_sql, N'@_dataAreaId nvarchar(4)', @_dataAreaId = @_companyId  
    
    FETCH NEXT FROM curSqlDictionary INTO @_tableName
END

CLOSE curSqlDictionary
DEALLOCATE curSqlDictionary

Martin F