April 23, 2010
Often I need to find all the tables in Dynamics GP that have a particular column or search for all columns where I only have a partial column name. Over the years I have seen lots of different code to accomplish this, so this is nothing new. But I am asked about this enough that I thought I would share the code I use:
SELECT
TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME in
(SELECT name
FROM sysobjects
WHERE xtype = 'U')
and COLUMN_NAME like '%xxxx%' -- replace with yours
ORDER BY COLUMN_NAME, TABLE_NAME
Just replace the xxxx with what you are looking for and run.
Updated on Jan 19, 2011 to add character maximum length.
Hi Victoria,
I used this query to find out POP and PM link, which is not available on Microsoft site. So I used this query. Following is the modified script.
SELECT
TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME in
(SELECT name
FROM sysobjects
WHERE xtype = ‘U’)
and COLUMN_NAME like ‘VCHRNMBR’ — replace with yours
ORDER BY COLUMN_NAME, TABLE_NAME
and my required result is POP30300.
Thanks for your help.
Hi Victoria,
Very helpful post.
Thanks
Waseem.
Hi Victoria,
Thanks for this new post. It is good and knowledable. But what is the meaning of xtype = U?
Thanks
Naseer,
xtype = ‘U’ is specifying that we only want user tables in the results. Here is a blog post I have found with a listing of all possible values for xtype: http://brainof-dave.blogspot.com/2006/11/xtype-values-in-sysobjects.html.
-Victoria