Dynamics GP - Jordan Jimenez

Find Any Database Value

Found this script in 2013 and I probably use it once or twice a week. Whenever I have trouble finding a table where a value is stored in Dynamics GP, I use this script to isolate the value. Just plug in the "Value" you are searching for below within the % symbols after @ValueToSeek. You can also search a specific type of table as well after the @TablePattern variable. 

DECLARE @tableNamePattern varchar(20)

DECLARE @valueToSeek varchar(50)



How to use:


change the @tableNamePattern to the prefix for the module that you want to search--

      for example RM for receivables, PM for payables, or no pattern to search the entire db.


change the @valueToSeek to some specific piece of data you are looking for from a character field.

      I typically use a voucher number, a document number, some unique description or comment.


Run the script against the database you want to search.


This script will return a list of tablenames, and fieldnames 

      that match the @tableNamePattern and @valueToSeek 



select @tableNamePattern = '%', @valueToSeek = 'TRIBRIDGE'

--select @tableNamePattern = @tableNamePattern + '%'

DECLARE @id int, @tn varchar(255), @fn varchar(255), @stmt varchar(255)


CREATE TABLE #list (tablename varchar(255), fieldname varchar(255), id int IDENTITY(1,1))


insert into #list (tablename, fieldname)

select o.name, c.name

FROM sysobjects o, syscolumns c

where o.id = c.id

      AND o.type = 'U'

      AND o.name like @tableNamePattern

      AND c.xtype in (175,239,231,167)    --character types


WHILE (Select count(*) FROM #list) > 0


      select @id = min(id) FROM #list


      select @tn = tablename, @fn = fieldname

            FROM #list

            WHERE id = @id


      select @stmt = 'IF EXISTS (SELECT * FROM [' + @tn + '] WHERE [' + @fn + ']=''' + @valueToSeek + ''')'

      select @stmt = @stmt + ' Select ''' + @tn + ''',''' + @fn + ''''


      EXEC (@stmt)


      delete from #list where id = @id

      IF (convert(real, @id)/10.0 = @id/10)

            PRINT @id


drop table #list