Find orphan note records in a Dynamics GP company database

About the Dynamics GP Notes table

Notes are held within the SY03900 company table in Dynamics GP. Every note in that notes table has a NOTEINDX, which is used by other tables in GP to reference that note. This makes the notes system extensible, when new modules/addins are created for GP,  those modules can simply piggy back off the existing notes table for their notes too by inserting notes into the table and referring to them by index. However this does have the disadvantage that looking at a random note in the notes table, you have no idea as to what records may be referencing that note.

Hence, if you were to delete records from tables that reference note records, it orphans any note record for that deleted record. The note record can still exist, but the record that refers to it has been removed. An example of where this could happen is, if someone were to delete a sales order line using SQL DELETE, without also deleting the note record in SY03900 that is referenced by that sales order line record.

You see, the note table does not have a "source" field to identify the "owner" table, so there is nothing in the note record to indicate from what table the note record originated. Thus you must check all the NOTEINDX fields in all the tables over the entire company database in order to find its owner.

Lucky for us, using dynamic SQL we can do this. We can query SQL server for the table definitions of all the tables in the database containing the field named NOTEINDX. We can then copy all the values that exist from each of those tables found into one big list of NOTEINDX values. Finally we can compare the notes table with that extracted big list of all valid values and find where we have a value in the notes table that does not exist in any of the tables in the GP company database. These will (most likely) be orphan records. Note you must exclude the SY03900 table itself when preparing this consolidated list!

Dynamic SQL to find all references to note records

The following script does this. It looks for any table that have a field named NOTEINDX and inserts all the note index numbers from those tables and fields into a temp table, from where you may join it back to the notes table to find the notes that no longer have reference in the database (orphaned).

If you are then going to use the results from this to delete notes, beware as if you have a third party product that uses notes but does not name its reference to the note as NOTEINDX, so I'm saying use this with care, especially if you start removing notes based on it, check what they are first and gain confidence they are genuine orphans.

IF EXISTS (  
SELECT \*  
FROM tempdb..sysobjects  
WHERE NAME = '##NotesConsolidated'  
)  
DROP TABLE dbo.##NotesConsolidated  

SET NOCOUNT ON  

CREATE TABLE ##NotesConsolidated (  
NOTEINDX numeric(19,5)  
,TableName VARCHAR(1000)  
)  
GO  
DECLARE @SqlStatement VARCHAR(500)  
DECLARE @DatabaseName AS VARCHAR(5)  
SET @DatabaseName = cast(db\_name() AS VARCHAR(5))  

DECLARE This\_cursor CURSOR  
FOR  
SELECT 'INSERT INTO ##NotesConsolidated (NOTEINDX, TableName) select NOTEINDX, ''\[' + SCHEMA\_NAME(schema\_id) + '\].\[' + t.NAME + '\]'' from \[' + SCHEMA\_NAME(schema\_id) + '\].\[' + t.NAME + '\]'  
FROM sys.tables AS t  
INNER JOIN sys.columns c ON t.OBJECT\_ID = c.OBJECT\_ID  
WHERE c.NAME = 'NOTEINDX' AND NOT (t.name='SY03900' AND SCHEMA\_NAME(schema\_id)='dbo');  
OPEN This\_cursor  

FETCH NEXT  
FROM This\_cursor  
INTO @SqlStatement  

WHILE (@@fetch\_status <> - 1)  
BEGIN  
EXEC (@SqlStatement)  

FETCH NEXT  
FROM This\_cursor  
INTO @SqlStatement  
END  
DEALLOCATE This\_cursor  

SELECT \* FROM SY03900  
where NOTEINDX not in (  
SELECT NOTEINDX FROM ##NotesConsolidated )

Results

Examples of the four hundred and seven (yours will differ) tables containing references to notes in the notes table:

About the Dynamics GP Notes table

Notes are held within the SY03900 company table in Dynamics GP. Every note in that notes table has a NOTEINDX, which is used by other tables in GP to reference that note. This makes the notes system extensible, when new modules/addins are created for GP,  those modules can simply piggy back off the existing notes table for their notes too by inserting notes into the table and referring to them by index. However this does have the disadvantage that looking at a random note in the notes table, you have no idea as to what records may be referencing that note.

Hence, if you were to delete records from tables that reference note records, it orphans any note record for that deleted record. The note record can still exist, but the record that refers to it has been removed. An example of where this could happen is, if someone were to delete a sales order line using SQL DELETE, without also deleting the note record in SY03900 that is referenced by that sales order line record.

You see, the note table does not have a "source" field to identify the "owner" table, so there is nothing in the note record to indicate from what table the note record originated. Thus you must check all the NOTEINDX fields in all the tables over the entire company database in order to find its owner.

Lucky for us, using dynamic SQL we can do this. We can query SQL server for the table definitions of all the tables in the database containing the field named NOTEINDX. We can then copy all the values that exist from each of those tables found into one big list of NOTEINDX values. Finally we can compare the notes table with that extracted big list of all valid values and find where we have a value in the notes table that does not exist in any of the tables in the GP company database. These will (most likely) be orphan records. Note you must exclude the SY03900 table itself when preparing this consolidated list!

Dynamic SQL to find all references to note records

The following script does this. It looks for any table that have a field named NOTEINDX and inserts all the note index numbers from those tables and fields into a temp table, from where you may join it back to the notes table to find the notes that no longer have reference in the database (orphaned).

If you are then going to use the results from this to delete notes, beware as if you have a third party product that uses notes but does not name its reference to the note as NOTEINDX, so I'm saying use this with care, especially if you start removing notes based on it, check what they are first and gain confidence they are genuine orphans.

IF EXISTS (  
SELECT \*  
FROM tempdb..sysobjects  
WHERE NAME = '##NotesConsolidated'  
)  
DROP TABLE dbo.##NotesConsolidated  

SET NOCOUNT ON  

CREATE TABLE ##NotesConsolidated (  
NOTEINDX numeric(19,5)  
,TableName VARCHAR(1000)  
)  
GO  
DECLARE @SqlStatement VARCHAR(500)  
DECLARE @DatabaseName AS VARCHAR(5)  
SET @DatabaseName = cast(db\_name() AS VARCHAR(5))  

DECLARE This\_cursor CURSOR  
FOR  
SELECT 'INSERT INTO ##NotesConsolidated (NOTEINDX, TableName) select NOTEINDX, ''\[' + SCHEMA\_NAME(schema\_id) + '\].\[' + t.NAME + '\]'' from \[' + SCHEMA\_NAME(schema\_id) + '\].\[' + t.NAME + '\]'  
FROM sys.tables AS t  
INNER JOIN sys.columns c ON t.OBJECT\_ID = c.OBJECT\_ID  
WHERE c.NAME = 'NOTEINDX' AND NOT (t.name='SY03900' AND SCHEMA\_NAME(schema\_id)='dbo');  
OPEN This\_cursor  

FETCH NEXT  
FROM This\_cursor  
INTO @SqlStatement  

WHILE (@@fetch\_status <> - 1)  
BEGIN  
EXEC (@SqlStatement)  

FETCH NEXT  
FROM This\_cursor  
INTO @SqlStatement  
END  
DEALLOCATE This\_cursor  

SELECT \* FROM SY03900  
where NOTEINDX not in (  
SELECT NOTEINDX FROM ##NotesConsolidated )

### Results

Examples of the four hundred and seven (yours will differ) tables containing references to notes in the notes table: