DEV Community


Discussion on: Find a Specific Column In An Unknown SQL Table

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 as schemaname, 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
    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 not like 'dt%' 
    and t.is_ms_shipped = 0
    and i.object_id > 255 
group by,, p.Rows
order by

rachelsoderberg profile image
Rachel Soderberg Author

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!

Forem Open with the Forem app