DEV Community

Find a Specific Column In An Unknown SQL Table

Rachel Soderberg on January 07, 2019

My company uses Aptean Intuitive ERP as part of our sales processing system and the "middle man" between Intuitive and our processing applications ...
Read full post
Collapse
 
helenanders26 profile image
Helen Anderson • • Edited

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
Collapse
 
rachelsoderberg profile image
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!

Collapse
 
stevcooo profile image
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....

Collapse
 
rachelsoderberg profile image
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.