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)

 
SET NOCOUNT ON


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

BEGIN

      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


END


drop table #list

SET NOCOUNT OFF