Monthly Archives: September 2018

Calculate column data useages

-- SQL Template use CTRL+SHIFT+M to fill the parameters
use <your_database_name, sysname, your_database_name>
GO
-- don't forget to delete de last union all statement
select '
ObjName', 'set transaction isolation level read uncommitted; ' union all
select distinct
concat(table_catalog,'.',table_schema,'.',table_name)
,concat('select ','''',table_schema+'.'+table_name+'.'+COLUMN_NAME,''' as ObjName','
,sum(cast(DATALENGTH(',COLUMN_NAME,') as bigint)/(1024.0*1024.0*1024.0)) as GB 
from ', quotename(TABLE_CATALOG),'.',quotename(table_schema),'.', quotename(table_name),' union all')
from INFORMATION_SCHEMA.COLUMNS as c 
join sys.tables t on c.TABLE_NAME = t.name
where TABLE_NAME = <your_table_name,sysname, your_table_name>