SQL Server String Length

Question

I am trying to determine the length of a string 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 do not know the definition of the table (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. 

A look at the sp_help stored procedure does not give me any hints.

What is the correct CREATE TABLE statement to insert sp_help results?

Answer 1

How is this done?

 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 the following:

- summarize the 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 the returned items

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

However, there are no guarantees, but it seems the same length is displayed in sp_help 'table'

DISCLAIMER: Please note that I read an article related to John Rudy, and in addition to the maximum sizes here, you also need other things like NULL bitmap to get the actual line size. Also, the dimensions are maximum. If you have a varchar column, the actual size is smaller for most rows ....

Vendoran has a nice solution, but I don't see the maximum row size anywhere (based on the definition of the table). I see the average size and all kinds of placement information, which is exactly what you need to estimate the size of the database for most things.

If you are only interested in what sp_help returns for length and adds it, I think (I'm not 100% sure) that a request to sysobjects returns the same numbers. Do they represent the full maximum row size? No, you are missing things like NULL bitmap. Are they a realistic indicator of your evidence? Not. VARCHAR (500) does not accept 500 bytes if you only store 100 characters. Also, TEXT fields and other fields stored separately from the line do not display their actual size, just the size of the pointer.

Answer 3

None of the above answers are correct or valid.

The question is to determine the number of bytes consumed by each row for each type of column data.

The only method (s) that I have is:

• exec sp_help 'mytable' - then add the Length field of the second result set (if you are working in Query Analyzer or Management Studio - just copy and paste the result into the spreadsheet and execute SUM)

• Write a C # or VB.NET program that accesses the second set of results and sums the Length field of each line.

• Change the sp_help code.

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

FWIW: definitions of result tables can be found here:

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

Answer 4

I cannot help you create a temporary table to store sp_help information, but I can help you calculate the lengths of the rows. Check out the MSDN article ; this will help you calculate such data based on field length, type, etc. It probably won't take too much to convert it to an SQL script that you could reuse by accessing sysobjects, etc.

EDIT:

I am editing my sentence to make a script for it. My path was not as easy as Wendoran. :)

Aside, I take back what I said earlier that I cannot help with the temporary table. I can: you cannot do this. sp_help prints seven sets of strings, so I don’t think you can do something as originally described in the original question. I think you are stuck using another method to come up with this.

Answer 5

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 returned data, you will understand that you can write a query using the case to calculate the size of the strings in bytes. It has everything you need: a data type | size | accuracy. BOL has bytes used by each data type. I will send a full answer when I have a chance.