Checking varbinary size at each sql tables

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 ?
motioneyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
sameer2010Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.