Most of the time spent designing a report, or writing some code against great plains is spent in finding the right table. I have written about this before.
Using Mark's Excel Sheets and Resource Descriptors
http://www.jivtesh.com/2007/10/finding-data-in-database-of-1000-tables.html
I also refer to -
SQL script that lets you search for a keyword in all tables of a database.
http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm
Today I found this on Mariano Gomez's blog -
SQL Script to find all tables which contain a particular Column -
http://dynamicsgpblogster.blogspot.com/2008/03/in-past-days-i-have-found-lot-of-people.html
The following example query attempts to solve the issue by exposing all tables where the account index (ACTINDX) column is found within the Fabrikam database (TWO)
select distinct rtrim(objs.name)
from syscolumns cols
inner join sysobjects objs on (cols.id = objs.id)
inner join sysindexes indx on (cols.id = indx.id)
where (cols.name = 'ACTINDX') and (objs.xtype = 'U') and (indx.rowcnt <> 0)
The results are as shown below:
CM00100
CM20400
GL00100
GL00103
No comments:
Post a Comment