SQL query to get row size in SQL Server

Below is a SQL query to find row size. The query uses DATALENGTH function, which returns the number of bytes used to represent a column data. A row may consist of fixed, variable data types. A varchar is a variable datatype which means that a varchar(50) column may contain a value with only 20 characters. The DATALENGTH function comes handy here.

IF object_id('sp_GetRowSize') is not null
drop procedure sp_GetRowSize
GO
CREATE procedure sp_GetRowSize(@Tablename varchar(100),@pkcol varchar(100))
AS 
BEGIN
declare @dynamicsql varchar(1000)

-- A @pkcol can be used to identify max/min length row
set @dynamicsql = 'select ' + @PkCol +' , (0'

-- traverse each record and calculate the datalength
select @dynamicsql = @dynamicsql + ' + isnull(datalength(' + name + '), 1)' 
	from syscolumns where id = object_id(@Tablename)
set @dynamicsql = @dynamicsql + ') as rowsize from ' + @Tablename + ' order by AddressID'


exec (@dynamicsql)

END

The above query creates a procedure. The procedure accepts two parameters @Tablename and @pkcol. The tablename is the name of the table you want to find the row size for and the @pkcol is the either the id column of the primary key column which identifies a row uniquely.

-- Execute the procedure
sp_getrowsize 'Person.Address','AddressID'

1_sql query to get row size in sql server

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook

Leave a Reply

Your email address will not be published. Required fields are marked *