Fetching Note Text field values from Dynamics GP tables

GetNoteText

DECLARE @noteindx nvarchar(20)
DECLARE @tablename nvarchar(20)
DECLARE @sqlstring nvarchar(MAX)
DECLARE @param nvarchar(100)
DECLARE @RC int

DECLARE @PrimaryKey1 VARCHAR(max) ='d.NOTEINDX',
@sql        NVARCHAR(max)

If Object_ID('tempdb..#TempNotesTable') is not null  
	Drop Table #TempNotesTable


 Create Table #TempNotesTable
 (  
   NOTEINDX numeric(19,5),
   TXTFIELD text,
   TableName VarChar(21),
   Detail varchar(200) 
 )

DECLARE notecursor CURSOR FOR

SELECT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c
LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME
WHERE c.COLUMN_NAME = 'NOTEINDX' AND t.TABLE_TYPE = 'BASE TABLE'
AND t.TABLE_NAME not like '_BK%' and t.TABLE_NAME not like 'bk%' and t.TABLE_NAME  not like'_UP%' and t.TABLE_NAME  not like '%_BK%'
and t.TABLE_NAME not like '%2024' and t.TABLE_NAME not like '%2023'
and t.TABLE_NAME not like '[a-z][a-z][0-9][0-9][0-9][0-9][0-9][_]%'
--and t.TABLE_NAME  like 'SOP%'
--and t.TABLE_NAME  in('SOP10100','SOP30100')
ORDER BY c.TABLE_NAME

OPEN notecursor
FETCH NEXT FROM notecursor INTO   @tablename


WHILE @@FETCH_STATUS = 0
BEGIN
     --select t.TXTFIELD,t.NOTEINDX,'''+ @tablename +''' as Tablename from SY03900 t inner join '+ @tablename +' n on t.NOTEINDX=n.NOTEINDX
			SET @sqlstring ='if exists( select t.TXTFIELD from SY03900 t inner join '+ @tablename +' n on t.NOTEINDX=n.NOTEINDX) begin
				SELECT @PrimaryKey1 +='', d.''+COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = '''+ @tablename+ ''' AND CONSTRAINT_NAME LIKE ''PK%'' 	end'
		
			EXEC Sp_executesql  @sqlstring,  N'@PrimaryKey1 varchar(1000) output',@PrimaryKey1 output
			--print @PrimaryKey1
			if len(@PrimaryKey1)>11 begin
				EXEC('SELECT '+@PrimaryKey1+',t.TXTFIELD, '''+  @tablename +''' as TableName  from '+  @tablename +' d inner join SY03900 t on d.NOTEINDX=t.NOTEINDX' )
			end
			
			--print ('INSERT INTO #TempNotesTable(NOTEINDX ,TXTFIELD,TableName,Detail)
			--select d.NOTEINDX ,t.TXTFIELD,'''+  @tablename +''' ,'+@PrimaryKey1+'  from '+  @tablename +' d inner join SY03900 t on d.NOTEINDX=t.NOTEINDX')

			--Exec ('INSERT INTO #TempNotesTable(NOTEINDX ,TXTFIELD,TableName,Detail)
			--select d.NOTEINDX ,t.TXTFIELD,'''+  @tablename +''' ,'+@PrimaryKey1+'from '+  @tablename +' d inner join SY03900 t on d.NOTEINDX=t.NOTEINDX'   )
			
			--select * from  #TempNotesTable

			SET  @PrimaryKey1= 'd.NOTEINDX'
      
       FETCH NEXT FROM notecursor INTO @tablename
END

CLOSE notecursor
DEALLOCATE notecursor