For my first issue of "This Week I Learned" I wanted to describe a question I had on the job this week that I never needed to approach academically or in my personal projects, and the solution I used to resolve it.
First a little introduction: 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:
c.name AS 'ColumnName'
,t.name AS 'TableName'
sys.tables t ON c.object_id = t.object_id
c.name LIKE '%SOD_DiscPercent%'
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.