Find a Specific Column In An Unknown SQL Table

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.

Comments

[Helen Anderson]

Great tip! The more I dig into those sys tables the more handy hints I find :)

I assume you are using SQL Server?

My favourite of the moment is this script that shows schema name, table name, row counts, total space and unused space

select 
    s.name as schemaname,
    t.name as tablename,
    p.rows as rowcounts,
    sum(a.total_pages) * 8 as totalspacekb, 
    cast(round(((sum(a.total_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as totalspacemb,
    sum(a.used_pages) * 8 as usedspacekb, 
    cast(round(((sum(a.used_pages) * 8) / 1024.00), 2) as numeric(36, 2)) as usedspacemb, 
    (sum(a.total_pages) - sum(a.used_pages)) * 8 as unusedspacekb,
    cast(round(((sum(a.total_pages) - sum(a.used_pages)) * 8) / 1024.00, 2) as numeric(36, 2)) as unusedspacemb
from
    sys.tables t
inner join     
    sys.indexes i on t.object_id = i.object_id
inner join
    sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
inner join
    sys.allocation_units a on p.partition_id = a.container_id
left outer join
    sys.schemas s on t.schema_id = s.schema_id
where
    t.name not like 'dt%' 
    and t.is_ms_shipped = 0
    and i.object_id > 255 
group by 
    t.name, s.name, p.Rows
order by
    t.name

[Rachel Soderberg]

I am using SQL Server, yep!

This was my first dive into sys tables, it's nothing that was discussed in my studies, so I appreciate your script as well. I'm going to keep that one in mind because I'm sure it will come in handy in the future. Thanks for sharing!

[Stevan Kostoski]

Hello Rachel,
I'm really happy when I see some Junior Developer sharing some experience with the community. A few years ago I have the same problem as yours, and I ended up with writing the same SQL as yours. You can even upgrade your query so it can be set up as a shortcut. You can see my code and example here github.com/stevcooo/SQL/tree/maste....

[Rachel Soderberg]

I appreciate that - I actually hesitated on writing technical articles and a blog for awhile because I thought to myself "nobody's going to find any value in something a new Junior developer writes!" But I realized that even if nobody finds value in reading it, I will at least find value in having exercised my technical writing and communication muscles.

Also thank you for the shortcut! That's a fantastic trick and I'm going to play around with what else I can shortcut in the same way.