Dynamics Database

(0) Share

Report

Posted on by Community Member

Can someone tell me what tables in the Dynamics DB I can run a query on and get the column name and the tables that use that column. I don't want to use resource description because I dont' believe there is a way to print the info out but I could be wrong.

Thank you

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

mpolino on at

Like (0)

Report

It doesn't exist in the Dynamics Database. The table information is not stored in way that you query from SQL. However, it is available in Excel format from DynamicAccounting.net at: http://www.box.net/shared/duvbizv2m5

This version is for GP 2010 and include tables and field names.

Mark

Was this reply helpful? Yes No
MG-16101311-0 26,225 on at

Like (0)

Report

You may also install the Support Debugging Tool for free and use the Resource Information window to get information about tables and fields.

http://blogs.msdn.com/b/developingfordynamicsgp/archive/2009/08/07/support-debugging-tool.aspx

Was this reply helpful? Yes No
Richard Whaley 25,195 on at

Like (0)

Report

You have been given good advice on where to find your information. One problem you will have, however, is the use of several different field names for the same information. For example, I think you will find multiple field names for component (child item in BOMs)

Was this reply helpful? Yes No
Dedy Alamsyah 510 on at

Like (0)

Report

Hi Michael !
if you just want to know what really is database that already executed by GP
simply you just running "Profiler" that built in installed with Microsoft SQL Server
this trick is working for me ..and you too .

Was this reply helpful? Yes No
Nate Owens 400 on at

Like (0)

Report

Had to do just this thing (ended up writing a script that takes this to the next level allowing searching/querying on the fields in question), this should get you by:

SELECT SYSOBJECTS.Name AS TableName, SYSCOLUMNS.Name AS FieldName

FROM <DB Name>.dbo.SYSCOLUMNS JOIN <DB Name>.dbo.SYSOBJECTS ON SYSCOLUMNS.ID = SYSOBJECTS.ID

WHERE SYSCOLUMNS.Name = 'fieldname here'

Was this reply helpful? Yes No
MG-16101311-0 26,225 on at

Like (0)

Report

This will only allow you to query physical column names and not be able to search by say, the actual field name as displayed in GP. Second drawback, not all users have access to SQL Server tools.

Was this reply helpful? Yes No
Victoria Yudin 22,769 on at

Like (0)

Report

Using the Support Debugging Tool is now my favorite way of getting GP resource information. I like it so much I put together step by step instructions on how to use it with the code David Musgrave has published to get all this data in Excel: http://victoriayudin.com/2010/07/23/new-resource-to-find-dynamics-gp-table-and-field-information/.

Was this reply helpful? Yes No
Dawn Mulligan 455 on at

Like (0)

Report

In reference to the Dynamics GP resource descriptions, you can print them fine, just hit the print button and select print to file, as a text file, then bring into Excel as fixed width, adjust columns a little, and it works great!

Was this reply helpful? Yes No