Ever wondered what UD tables have data? Here is a good SQL query that can help:
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
AND QUOTENAME(sOBJ.name) LIKE '%UD%'
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY [TableName]
Thanks for that Jason, though you’re a few weeks late! I’ve just finished writing a register for our UD tables so we can track which are used and for what purpose.
The biggest problem I had was, we have had a couple of Epicor managers and a handful of different consultants during our Epicor journey. We have tables that are in use but don’t know if the data is still relevant. Since we’ve had the ability to create UD fields in our regular tables I get the feeling a few of our UD tables have been superseded or were used in reports that are no longer in use. This highlights the need for documenting the intention and implementation of customisation, or it’s poor chumps like me who have to try to reverse-engineer other developers intentions. I have at least two UD tables that have data in them which nobody can seem to identify. This effectively removes the tables from our pool of resources.
Does anyone know of any ingenious methods to identify if this data is being used? My current thoughts are to backup these tables, delete the data and wait for someone to scream…
Thanks for the Query Jason!
Just tested it with a little variation, still not perfect, to make it a bit more accurate on Ice.UD tables.
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
, SUM(sPTN.Rows) AS [RowCount]
FROM
sys.objects AS sOBJ
INNER JOIN sys.partitions AS sPTN
ON sOBJ.object_id = sPTN.object_id
INNER JOIN sys.schemas AS sch
ON sch.schema_id = sOBJ.schema_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
AND sch.name = 'Ice'
AND QUOTENAME(sOBJ.name) LIKE '%UD%'
GROUP BY
sOBJ.schema_id
, sOBJ.name
HAVING SUM(sPTN.Rows) > 0
ORDER BY [TableName]
