Working with both Spectrum and Vista databases I've often found myself frustrated and unable to find a table that holds the data I need for a Crystal Report I'm writing. Maybe you can relate? Modern databases are often large collects of hundreds if not thousands of tables and views. Each holding just a small amount of the whole pie. Viewpoint Vista and for that matter Sage 300 have tools to print a Data Dictionary. These are wonderful resources and I am very grateful they exists. However,
even these are not as good as a simple way to search through all tables for a specific field. Spectrum does have Function/Table Cross-Reference Report under Info Link but I've always found that shows way too many results and does not greatly reduce my frustration level.
While venting my frustration to a friend at the Spectrum offices in Seattle, I was given a tip that has saved me hours of frustration. If you have SQL Server Management Studio (SSMS) you can run a simple query looking for a field and the tables and views that hold such a field.
For example, earlier this week I was tasked with writing a custom Spectrum report showing all payments made against Accounts Payable Invoices. I'll be honest, I do not have all Spectrum tables and views memorized. But that was OK, I remembered this trick that could narrow down the tables I would want to review in detail.
I opened up SSMS, connected to a Spectrum database and pasted in this query.
SELECT TABLE_NAME AS 'TableName'
,COLUMN_NAME AS 'ColumnName'
WHERE TABLE_NAME LIKE 'VN_%' and COLUMN_NAME LIKE '%Payment%'
ORDER BY TableName , ColumnName;
You'll notice the "VN_" that is bold in the Where sentence and the "Payment" that is also bold in the same Where sentience. Both of these are the places you can put in the words your are looking for.
I first typed in the "VN_" to narrow the tables returned to just those that had Accounts Payable Vendor information. I then typed in "Payment" to find only those tables that had a field with Payment in the name. The "%" sign acts like a wildcard. Meaning that tables and fields may have fuller names then just that which I filtered for. By putting Payment inside the COLUMN_NAME wildcards, I was able to find fields like Payment Date and Payment Amount as examples.
Running this query on the Spectrum database still returned many tables but it put me on the right track. I eventually settled on the VN_PAYMENT_HISTORY_MC for the project I was doing. Below is a partial list of the Vendor tables returned, all of them having "Payment in a field name.
Feel free to copy the query above, alter it as you want and put it to good use. I promise you it will reduce your frustration by leaps and bounds.