-->

Monday, March 31, 2008

Searching for Data in a Large SQL Database like Great Plains

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: