compare

The other day I had a requirement to identify and delete any objects in the AX 2012 SQL database which are custom made by other means than AX DB synchronize. I am excluding all tables and views which exists in the ModelStore (if you have your model DB separate, you might want to change ModelElement to [YourAXDB_model].[dbo].[ModelElement] to get it working). Also we had to exclude a bunch of kernel-level tables and views. Here is a T-SQL statement which could find SQL tables and views not part of the AOT in AX:

SELECT obj.type, obj.name, obj.create_date, sum(au.total_pages)*8 AS [Space_in_KB] FROM sys.objects AS obj
JOIN sys.partitions AS p ON p.object_id = obj.object_id
JOIN sys.allocation_units AS au ON au.container_id = p.hobt_id
WHERE is_ms_shipped = 0 AND obj.type IN ('U', 'V')
AND NOT EXISTS (
	SELECT *
		FROM ModelElement WITH (NOLOCK)
	WHERE ModelElement.Name COLLATE Latin1_General_CI_AS = obj.name COLLATE Latin1_General_CI_AS
		AND ModelElement.ElementType = 44
	)
AND obj.name NOT LIKE 'Model%'
AND (
	(obj.type = 'U'
		AND obj.name NOT LIKE 'AxId%'
		AND obj.name NOT LIKE 'DATA%'
		AND obj.name NOT LIKE 'Layer%'
		AND obj.name NOT LIKE 'PrintJob%'
		AND obj.name NOT LIKE 'SECURITY%'
		AND obj.name NOT LIKE 'Source%'
		AND obj.name NOT LIKE 'SQL%'
		AND obj.name NOT LIKE 'SYS%'
		AND obj.name NOT LIKE 'TIMEZONE%'
		AND obj.name NOT LIKE 'USER%'
		AND obj.name NOT IN ('DEL_ACCESSRIGHTSLIST',
			'DEL_COMPANYDOMAINLIST',
			'DEL_DOMAININFO',
			'DEL_USERGROUPINFO',
			'DEL_USERGROUPLIST',
			'ElementTypes',
			'existingPaths',
			'GlobalFieldIdPool',
			'myModel',
			'myParents',
			'newPaths',
			'PLAN_',
			'SchemaVersion',
			'TABLECOLLECTIONLIST',
			'VIRTUALDATAAREALIST',
			'VSASSEMBLY'
			)
	)
	OR (obj.type = 'V'
		AND obj.name NOT LIKE 'Secur%'
		AND obj.name NOT LIKE 'SysModel%'
		AND obj.name NOT LIKE 'UTIL%'
		AND obj.name NOT IN ('ConfigurationKeys', 'LicenseCodes', 'Origins')
		)
	)
group by obj.type, obj.name, obj.create_date
ORDER BY obj.type, obj.name

The result shows all custom created objects with their creation date and size occupied in the DB, which are good candidates for removal. Here are my entries after running the job for SQL tables and views not part of the AOT. I can see that 4 tables have been duplicated, and a SQL database compression estimation script was also storing data – they can all be dropped: