Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to get list of tables with display name and physical name?

Posted on 2008-09-30
7
Medium Priority
?
450 Views
Last Modified: 2012-05-05
There are a few tedious ways to identify the physical table names and their corresponding display names in Great Plains using the interface, but I would like to be able to find the display name that corresponds to the physical name using a SQL query. That way I could use it with my query against the schema to display physical names, display names, and a list of columns and data types. I'm building an interface between GP and another system, and it would be nice not to have to use the Resource Descriptions, and determine the Product and Series before I can get the table associated with the display name. Great Plains, more like Great PAINS!

Thanks, supr
0
Comment
Question by:suprslackr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 

Author Comment

by:suprslackr
ID: 22609873
Aw, c'mon!

If nobody can answer my questions anymore, does that mean I'm an expert?    ;-)

supr
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22629026
Yes sorry, not familiar with the GREAT PLAINS data schema.

For SQL Server 2005, you can run a query against sysobjects and look for table names.

In my ERP system there is a metadata table that defines the domains of the application which represents things like Item for Item Master with information on the actual physical table IMA for example.  Check the listing of tables in GREAT PLAINS and see if something jumps out with keywords like data domains, metaXXXX, or try running SQL Profiler while application UI s loading and see if it runs any queries to map the presentation layer names to physical ones that you can pick up what table the mappings are being stored in.
-- http://technet.microsoft.com/en-us/library/ms177596.aspx
select o.[name]
from sysobjects o
where o.xtype = 'U'

Open in new window

0
 

Author Comment

by:suprslackr
ID: 22635775
Hi mwvisa1,

Thanks for taking the time to look at this question. I have already tried using a query against sysobjects to find this information, with no luck.

I gave Profiler a shot, and I was able to find physical names and techical names, but no display names.

Furthermore I ran the atatched snippet as a SP against the DYNAMICS and individual company tables. I used this proc to search for some of the display names that are in the title bars of the GP windows, but it didn't find them!

The only thing I can think of at this time is that these must be hard-coded into the Dexterity source? This makes no sense to me whatsoever, but I'm completely stumped. I'm getting the feeling that there is no way to do this by the means I have available. I'll leave this question open for a few more days, but I think I'm going to have to do it the hard way.

Thanks again,

supr
CREATE PROC SearchAllTables
(
	@SearchStr nvarchar(100)
)
AS
BEGIN
 
	-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
	-- Purpose: To search all columns of all tables for a given search string
	-- Written by: Narayana Vyas Kondreddi
	-- Site: http://vyaskn.tripod.com
	-- Tested on: SQL Server 7.0 and SQL Server 2000
	-- Date modified: 28th July 2002 22:50 GMT
 
 
	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))
 
	SET NOCOUNT ON
 
	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
	SET  @TableName = ''
	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
 
	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
		(
			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
			FROM 	INFORMATION_SCHEMA.TABLES
			WHERE 		TABLE_TYPE = 'BASE TABLE'
				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
				AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
		)
 
		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
		BEGIN
			SET @ColumnName =
			(
				SELECT MIN(QUOTENAME(COLUMN_NAME))
				FROM 	INFORMATION_SCHEMA.COLUMNS
				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2)
					AND	TABLE_NAME	= PARSENAME(@TableName, 1)
					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
					AND	QUOTENAME(COLUMN_NAME) > @ColumnName
			)
	
			IF @ColumnName IS NOT NULL
			BEGIN
				INSERT INTO #Results
				EXEC
				(
					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
					FROM ' + @TableName + ' (NOLOCK) ' +
					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
				)
			END
		END	
	END
 
	SELECT ColumnName, ColumnValue FROM #Results
END

Open in new window

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22635944
Yes unfortunately, they may have hardcoded in the UI code. :(

If you can't find a reference table, maybe you can at least go through this exercise once creating your own reference table of physical to logical names and then use your metadata table going forward.

Best of luck to you.

Regards,
Kevin
0
 

Author Comment

by:suprslackr
ID: 22668405
Thanks for your help, Kevin. I may go ahead and make a table that maps the names as you said. While it doesn't help me for my immediate problem (more of an annoyance, really), it may help in the future to make things go more smoothly.

While I didn't really get an answer for my question, I think the suggestion to try Profiler was a good one, and it was something I had not used before you suggested it. I'm goign to give you the points for trying, and for giving me some great tips to use from now on.

Thanks,
supr
0
 

Author Closing Comment

by:suprslackr
ID: 31504199
Thanks for your help, Kevin. I may go ahead and make a table that maps the names as you said. While it doesn't help me for my immediate problem (more of an annoyance, really), it may help in the future to make things go more smoothly.

While I didn't really get an answer for my question, I think the suggestion to try Profiler was a good one, and it was something I had not used before you suggested it. I'm goign to give you the points for trying, and for giving me some great tips to use from now on.

Thanks,
supr
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22668752
You are welcome and thank you.

Good luck!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part I
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question