Saturday, April 3, 2010

Get Row Size in SQL Server

I don't know if there's a built-in storedproc or function to determine each row size. Here's one way to do it. Get all column names and add each column's data length.






declare @cs varchar(8000);

select @cs = coalesce(@cs + ' + DATALENGTH(' + column_name + ')', 'DATALENGTH(' + column_name + ')')
from INFORMATION_SCHEMA.COLUMNS i where TABLE_NAME = 'Members';

declare @qry nvarchar(4000);

set @qry = 'select Length = ' + @cs + ', * from members ';
select @qry;
exec sp_executesql @qry;


Length  MemberID  LoginName    ReputationPoints
16      1         John         1
16      2         Paul         1
20      3         George       1
18      4         Ringo        1

No comments:

Post a Comment