My company uses Aptean Intuitive ERP as part of our sales processing system and the "middle man" between Intuitive and our processing applications is the SQL database. Our homegrown processor application takes in data from the inbound sales files, inserts the data into the corresponding SQL columns and creates PDF item and sales order files, then Intuitive processes these PDF files and inserts data into the system.

One of my tasks this week was to add Discounts to this chain of events because in its current state the manager must go in and manually update the tables and Intuitive for each order with a discount.

To accomplish this task I first determined where discounts were being managed in Intuitive and what their corresponding column names were so I could make sure the proper table values were being updated when the incoming sales file is processed. After determining the column names I came upon the question I've never had to ask before, "how do I determine which table holds a specific column without searching through all 100+ tables manually?"

After a little digging I discovered a solution that can be done in a rather simple SQL query:
 SELECT
  c.name AS 'ColumnName'
  ,t.name AS 'TableName'
 FROM
  sys.columns c
 JOIN
  sys.tables t ON c.object_id = t.object_id
 WHERE
  c.name LIKE '%SOD_DiscPercent%'
 ORDER BY
  TableName, ColumnName

To break down this query a bit:
 sys.columns returns a row for each column of a table or view
 sys.tables returns a row for each table

Joining sys.columns and sys.tables on the object ID and filtering by the column name lets us use our known column to determine which table it belongs to. Using SELECT all would show even more information about your table and column, but for this example I was only concerned with getting a table name.

Using this query I was able to determine which tables need to be updated in the processor application before the item and sales order PDFs are created so the proper fields are updated once it reaches Intuitive.