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

I can't help you with creating a temp table to store sp_help information, but I can help you with calculating row lengths. Check out this MSDN article; it helps you calculate such based on the field lengths, type, etc. Probably wouldn't take too much to convert it into a SQL script you could reuse by querying against sysobjects, etc.

EDIT:

I'm redacting my offer to do a script for it. My way was nowhere near as easy as Vendoran's. :)

As an aside, I take back what I said earlier about not being able to help with the temp table. I can: You can't do it. sp_help outputs seven rowsets, so I don't think you'll be able to do something as initially described in the original question. I think you're stuck using a different method to come up with it.

Answer 2

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 3

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.

Answer 4

--This will give you all the information you need

Select * into #mytables from INFORMATION_SCHEMA.columns

select * from #mytables

drop table #mytables

UPDATE: The answer I gave was incomplete NOT incorrect. If you look at the data returned you'd realize that you could write a query using case to calculate a rows size in bytes. It has all you need: the datatype|size|precision. BOL has the bytes used by each datatype. I will post the complete answer when I a chance.

Answer 5

None of the aforementioned answers is correct or valid.

The question is one of determining the number of bytes consumed per row by each column's data type.

The only method(s) I have that work are:

1. exec sp_help 'mytable' - then add up the Length field of the second result set (If working from Query Analyzer or Management Studio - simply copy and paste the result into a spreadsheet and do a SUM)

2. Write a C# or VB.NET program that accesses the second resultset and sums the Length field of each row.

3. Modify the code of sp_help.

This cannot be done using Transact SQL and sp_help because there is no way to deal with multiple resultsets.

FWIW: The table definitions of the resultsets can be found here:

http://msdn.microsoft.com/en-us/library/aa933429(SQL.80).aspx