How do I determine the row size of all tables in a particular DB?

Question

How do I determine the row size of all tables in a particular DB?

Greg J

SSCarpal Tunnel

Points: 4288
More actions
October 6, 2005 at 9:13 am

#102844

I have a feeling I'm exceeding 8060 on a lot of tables. I'd like a definitive list of tables I should be looking into.
Does anyone have a script that will show me this? Any ideas on system sprocs or extended sprocs that might get me started?
tx

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 1

I don't believe any row *can* exceed the 8K page length in SQL Server 2000. There is only a potential for an error, should any row *try* to exceed that length.

There may be a more precise method, but try this:

SELECT OBJECT_NAME (id) tablename
     , COUNT (1)        nr_columns
     , SUM (length)     maxrowlength
FROM   syscolumns
GROUP BY OBJECT_NAME (id)
ORDER BY OBJECT_NAME (id)

Greg J SSCarpal Tunnel Points: 4288 More actions · Answer 2

nice. that did the trick. and you're right, I was looking for the tables that *could* have exceeded the 8060 limit.

I was getting some system tables (some are over 8060!) as well as stored procedures the way you had it, here is my modified version:

SELECT OBJECT_NAME (sc.[id]) tablename

, COUNT (1) nr_columns

, SUM (sc.length) maxrowlength

FROM syscolumns sc

join sysobjects so

on sc.[id] = so.[id]

WHERE so.xtype = 'U'

GROUP BY OBJECT_NAME (sc.[id])

ORDER BY SUM (sc.length) desc

Lee Dise SSCarpal Tunnel Points: 4384 More actions · Answer 3

I'm glad that helped. If you want to find the largest *actual* row lengths for a table, something like the following would work in most cases:

DECLARE @sql        VARCHAR (8000)
        , @tablename VARCHAR (255)
        , @delim      VARCHAR (3)
        , @q          CHAR (1)
SELECT @tablename = '{table name}'
       , @q         = CHAR (39)
SELECT @delim = ''
       , @sql   = 'SELECT '
SELECT @sql   = @sql
                + @delim
                + 'DATALENGTH ([' + name + '])'
       , @delim = ' + '
FROM   syscolumns
WHERE id = OBJECT_ID (@tablename)
ORDER BY colid
SELECT @sql = @sql + ' rowlength'
              + ' FROM [' + @tablename + ']'
       , @sql = 'SELECT MAX (rowlength)'
              + ' FROM (' + @sql + ') rowlengths'
PRINT @sql
EXEC (@sql)

I say "in most cases", because if the number of columns or the lengths of their names add up to more the 8K characters, you won't get a complete query. You can code around that, though, by executing more than one T-SQL string and using "overflow" variables.

Michael Lee-169622 SSCommitted Points: 1721 More actions · Answer 4

Lee's script is great as long as you do not have a row with nulls. I modified the query as follows to eliminate that problem:

DECLARE @sql        VARCHAR (8000)
        , @tablename VARCHAR (255)
        , @delim      VARCHAR (3)
        , @q          CHAR (1)
SELECT @tablename = 'uspsorderbase'
       , @q         = CHAR (39)
SELECT @delim = ''
       , @sql   = 'SELECT '
SELECT @sql   = @sql
                + @delim
                + 'ISNULL(DATALENGTH ([' + name + ']),0)'
       , @delim = ' + '
FROM   syscolumns
WHERE id = OBJECT_ID (@tablename)
ORDER BY colid
SELECT @sql = @sql + ' rowlength'
              + ' FROM [' + @tablename + ']'
       , @sql = 'SELECT MAX (rowlength)'
              + ' FROM (' + @sql + ') rowlengths'
PRINT @sql
EXEC (@sql)

Shalom!,

Michael Lee

ruchir-628038 SSC Veteran Points: 260 More actions · Answer 5

Hi guys,

I had written a small sql scalar function that takes a table name and returns available bytes for column expansion

Notes:

1. The maximum size in bytes that a table can hold in SQL Server is 8060 including internal book keeping bytes

2. If there is even one variable column 2 bytes are added as internal bytes

3. A header of 4 bytes is always added to a row

4. Each variable column contribute 2 bytes towards the total internal bytes

5. Only the maximum bytes of all variable columns in the row are kept in the same table page – anything exceeding it is rolled out to an external page

Hope it helps

-- ================================================

-- Template generated from Template Explorer using:

-- Create Scalar Function (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- This block of comments will not be included in

-- the definition of the function.

-- ================================================

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:Ruchir T

-- Create date: 01/02/2008

-- Description:returns the number of bytes left to use for creating new columns

-- =============================================

CREATE FUNCTION available_tablerowsize

(

-- Add the parameters for the function here

@tablename char(50)

)

RETURNS int

AS

BEGIN

-- variables to track fixed and variable column sizes

DECLARE @num_columns int

DECLARE @result int

DECLARE @num_fixed_columns int

DECLARE @fixed_data_size int

DECLARE @var_data_size int

DECLARE @num_var_columns int

DECLARE @max_var_size int

DECLARE @null_bitmap_size int

DECLARE @row_size int

-- Find the total number of columns

select @num_columns = count(*)

from syscolumns,systypes