SQL Server Row Length

Question

I'm attempting to determine the row length in bytes of a table by executing the following stored procedure:

CREATE TABLE #tmp 
(
  [ID] int, 
  Column_name varchar(640), 
  Type varchar(640), 
  Computed varchar(640), 
  Length int, 
  Prec int, 
  Scale int, 
  Nullable varchar(640), 
  TrimTrailingBlanks varchar(640), 
  FixedLenNullInSource varchar(640), 
  Collation varchar(256)
)
INSERT INTO #tmp exec sp_help MyTable
SELECT SUM(Length) FROM #tmp
DROP TABLE #tmp

The problem is that I don't know the table definition (data types, etc..) of the table returned by 'sp_help.'

I get the following error:

Insert Error: Column name or number of supplied values does not match table definition.

Looking at the sp_help stored procedure does not give me any clues.

What is the proper CREATE TABLE statement to insert the results of a sp_help?

Answer 1

How doing it this way instead?

CREATE TABLE tblShowContig
(
    ObjectName CHAR (255),
    ObjectId INT,
    IndexName CHAR (255),
    IndexId INT,
    Lvl INT,
    CountPages INT,
    CountRows INT,
    MinRecSize INT,
    MaxRecSize INT,
    AvgRecSize INT,
    ForRecCount INT,
    Extents INT,
    ExtentSwitches INT,
    AvgFreeBytes INT,
    AvgPageDensity INT,
    ScanDensity DECIMAL,
    BestCount INT,
    ActualCount INT,
    LogicalFrag DECIMAL,
    ExtentFrag DECIMAL
)
GO

INSERT tblShowContig
EXEC ('DBCC SHOWCONTIG WITH TABLERESULTS')
GO

SELECT * from tblShowContig WHERE ObjectName = 'MyTable'
GO

Answer 2

Try this:

-- Sum up lengths of all columns

select SUM(sc.length)  
from syscolumns sc  
inner join systypes st on sc.xtype = st.xtype  
where id = object_id('table')

-- Look at various items returned

select st.name, sc.*  
from syscolumns sc  
inner join systypes st on sc.xtype = st.xtype  
where id = object_id('table')  

No guarantees though, but it appears to be the same length that appears in sp_help 'table'

DISCLAIMER: Note that I read the article linked by John Rudy and in addition to the maximum sizes here you also need other things like the NULL bitmap to get the actual row size. Also the sizes here are maximum sizes. If you have a varchar column the actual size is less on most rows....

Vendoran has a nice solution, but I do not see the maximum row size anywhere (based on table definition). I do see the average size and all sorts of allocation information which is exactly what you need to estimate DB size for most things.

If you are interested in just what sp_help returns for length and adding it up, then I think (I'm not 100% sure) that the query to sysobjects returns those same numbers. Do they represent the full maximum row size? No, you are missing things like the NULL bitmap. Do they represent a realistic measure of your actual data? No. Again VARCHAR(500) does not take 500 bytes if you only are storing 100 characters. Also TEXT fields and other fields stored separately from the row do not show their actual size, just the size of the pointer.