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


I also refer to -

SQL script that lets you search for a keyword in all tables of a database.


Today I found this on Mariano Gomez's blog -

SQL Script to find all tables which contain a particular Column -


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:


No comments: