Checking varbinary size at each sql tables

motioneye
motioneye used Ask the Experts™
on
How do I in sql server 2014 to check size of each tables that includes varbinary  column size? I have a large db that occupied around 500GB of db size, when I run to check what table that has more space, the query just  results with only 60GB data  for all tables. So I suspect each tables that has varbinary  column  that might use the space, so teh question how do I found what table that store biggest varbinary data ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Right click your Database -> Choose Reports -> Standard Reports -> Disk Usage by Top Tables.
This will give you the space used and allocated for each and every tables inside your database..

If it doesn't count to 500 GB, then it indicates that there are free space inside your data or log files which you can confirm by doing the below:
1. Right Click your Database -> Tasks -> Shrink -> Files and then see what is the currently allocated size and Available Free space..
Use this:
use yourdb
declare @tbl table(tblname varchar(200),colname varchar(200));
insert into @tbl
select a.name as tblname, 
b.name as colname
from sys.tables a, sys.columns b, sys.types c
where a.type='u'
and b.object_id = a.object_id
and b.system_type_id = c.system_type_id
and c.name = 'varbinary'

DECLARE tablecursor CURSOR FOR  select * from @tbl
declare @tblname varchar(200)
declare @colname varchar(200)
open tablecursor
declare @t table(tblname varchar(200), colname varchar(200), colsize bigint)
declare @strsql nvarchar(max)
FETCH NEXT FROM tablecursor INTO  @tblname, @colname

WHILE @@FETCH_STATUS = 0   
BEGIN  
	--select @tblname, @colname
	set @strsql = 'select '''+ @tblname +''',''' +@colname+''', coalesce(sum(DATALENGTH('+ @colname +')),0) from ' + @tblname
	print @strsql
    insert into @t(tblname,colname,colsize)
	exec sp_executesql @strsql

    FETCH NEXT FROM tablecursor INTO @tblname, @colname
END  
close tablecursor
deallocate tablecursor
select * from @t order by colsize desc

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial